Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,680 --> 00:00:08,440
It's time now for us to do exercise to.
2
00:00:09,010 --> 00:00:14,140
And in this exercise, we're going to practice some of the skills that we've learned in relation to
3
00:00:14,140 --> 00:00:15,490
look up functions.
4
00:00:16,360 --> 00:00:23,890
So we have a table on this worksheet that just shows us some athlete names, their bib numbers, the
5
00:00:23,890 --> 00:00:28,250
event that they run in the route that they took and the position.
6
00:00:28,270 --> 00:00:31,330
So this is really an athlete's finishing table.
7
00:00:31,600 --> 00:00:39,070
And what I'd like you to do is in Cell H three, I'd like you to create a data validation dropdown list
8
00:00:39,370 --> 00:00:41,860
that lists out all of the athletes.
9
00:00:42,340 --> 00:00:43,740
And just a quick tip here.
10
00:00:43,760 --> 00:00:48,880
All of the athletes in Column A are unique, so we don't have any duplicates.
11
00:00:49,180 --> 00:00:55,780
I then like to construct a formula using a lookup function of your choice so that when we select an
12
00:00:55,780 --> 00:01:02,860
athlete from the dropdown, it's going to return that bib number, the route they took and that position.
13
00:01:03,160 --> 00:01:07,600
And the final thing I'd like you to do is just add some error checking into your formula.
14
00:01:08,410 --> 00:01:15,400
So it might be that you add if error or maybe you add if an a just to make sure that if there is an
15
00:01:15,400 --> 00:01:20,110
error in this data, we're handling that error with a meaningful message.
16
00:01:20,380 --> 00:01:21,780
See how you get on with that.
17
00:01:21,790 --> 00:01:25,120
And if you want to see my answer, then please keep watching.
18
00:01:25,330 --> 00:01:31,810
So the first thing we need to do here is create a data validation dropdown list in Cell H three that
19
00:01:31,810 --> 00:01:33,370
lists out all of the athletes.
20
00:01:34,120 --> 00:01:40,120
So let's click in Cell H three after data and into data validation.
21
00:01:40,390 --> 00:01:48,340
Now we want to create a dropdown list, so let's select that from the menu and our souls is going to
22
00:01:48,340 --> 00:01:50,080
be the athlete names.
23
00:01:50,650 --> 00:01:54,250
It's a control shift down arrow to select all of them.
24
00:01:54,640 --> 00:01:55,420
Let's click on.
25
00:01:55,690 --> 00:02:01,990
OK, so now I should find that I have my little dropdown list with all of the athletes listed out.
26
00:02:02,230 --> 00:02:06,650
Time now to move on to and look up formula now for this example.
27
00:02:06,670 --> 00:02:14,410
I'm going to use index a match, but if you'd rather use the look up or even look up, then please feel
28
00:02:14,410 --> 00:02:14,800
free.
29
00:02:14,950 --> 00:02:18,310
So we're going to do index array.
30
00:02:18,520 --> 00:02:20,330
So what are we looking for here?
31
00:02:20,350 --> 00:02:22,330
We're looking for the bib number.
32
00:02:22,960 --> 00:02:30,070
So our array is going to be the bib number column before to be 27 comma.
33
00:02:30,820 --> 00:02:33,060
We now need to find the row number.
34
00:02:33,070 --> 00:02:38,890
So we're going to go straight in with our match because we're going to match the athlete name.
35
00:02:38,890 --> 00:02:40,630
That's going to be our look up value.
36
00:02:41,770 --> 00:02:49,300
We want to match the athlete name in the athlete name, column control, shift down arrow comma.
37
00:02:50,050 --> 00:02:57,790
We want to do an exact match of the athlete name, plans of match clothes of index and enter.
38
00:02:58,120 --> 00:03:00,190
Now it says an a in there.
39
00:03:00,490 --> 00:03:05,380
And if you remember, I said I'd like you to add some error checking into these formulas.
40
00:03:05,680 --> 00:03:10,990
So what I'm going to do is I'm going to go straight up to the formula bar and we're just going to wrap
41
00:03:10,990 --> 00:03:12,700
this in an if and A..
42
00:03:13,030 --> 00:03:19,930
And if we do come across Sunday, we wanted to say not found like so.
43
00:03:20,920 --> 00:03:27,540
So now if I select an athlete, I should find that it either tells me what their bib number is or if
44
00:03:27,550 --> 00:03:30,460
it can't find it in the table it's going to come up with not found.
45
00:03:30,710 --> 00:03:37,060
Let's complete our other two index and match formulas, so equals index.
46
00:03:37,690 --> 00:03:40,270
This time we're looking for the root.
47
00:03:40,840 --> 00:03:46,210
So that is going to be our array control shift down arrow comma.
48
00:03:47,140 --> 00:03:51,640
We automate the finding of the row number by using match and look up.
49
00:03:51,640 --> 00:03:54,490
Value is whatever we have in H three.
50
00:03:55,930 --> 00:04:05,230
We're looking up the athlete name in the athlete name column and we want to do an exact match, close
51
00:04:05,230 --> 00:04:08,110
of match, close of index enter.
52
00:04:08,650 --> 00:04:13,090
And remember, you could also add your error checking into this formula.
53
00:04:13,180 --> 00:04:14,620
Let's do the final one.
54
00:04:14,620 --> 00:04:18,550
This time I'm going to switch things up and I'm going to use a V lookup.
55
00:04:18,820 --> 00:04:25,150
This time I need my look up value, which I'm going to find in Self H three, my table array.
56
00:04:25,150 --> 00:04:27,700
Well, this is my table right over here.
57
00:04:27,730 --> 00:04:29,320
I'm going to select everything.
58
00:04:30,130 --> 00:04:36,470
Which column do I want to return what I want to return the position and counting from left to right.
59
00:04:36,490 --> 00:04:40,000
I can see that this is column number five.
60
00:04:40,300 --> 00:04:42,080
What type of match do I want to do?
61
00:04:42,100 --> 00:04:46,270
Well, I want to do an exact match, so we want a false argument on the end.
62
00:04:47,020 --> 00:04:50,620
Close the bracket, hit enter and I get my result.
63
00:04:51,280 --> 00:04:53,230
Let's double check to make sure this is correct.
64
00:04:53,240 --> 00:04:55,450
So let's find Donald Holland.
65
00:04:55,990 --> 00:04:58,330
His bib number is one zero nine zero.
66
00:04:58,540 --> 00:05:03,160
He ran the East Loop route and he finished in position number seven.
6582
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.