Original subtitles

It's time now for us to do exercise to.

And in this exercise, we're going to practice some of the skills that we've learned in relation to

look up functions.

So we have a table on this worksheet that just shows us some athlete names, their bib numbers, the

event that they run in the route that they took and the position.

So this is really an athlete's finishing table.

And what I'd like you to do is in Cell H three, I'd like you to create a data validation dropdown list

that lists out all of the athletes.

And just a quick tip here.

All of the athletes in Column A are unique, so we don't have any duplicates.

I then like to construct a formula using a lookup function of your choice so that when we select an

athlete from the dropdown, it's going to return that bib number, the route they took and that position.

And the final thing I'd like you to do is just add some error checking into your formula.

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

error in this data, we're handling that error with a meaningful message.

See how you get on with that.

And if you want to see my answer, then please keep watching.

So the first thing we need to do here is create a data validation dropdown list in Cell H three that

lists out all of the athletes.

So let's click in Cell H three after data and into data validation.

Now we want to create a dropdown list, so let's select that from the menu and our souls is going to

be the athlete names.

It's a control shift down arrow to select all of them.

Let's click on.

OK, so now I should find that I have my little dropdown list with all of the athletes listed out.

Time now to move on to and look up formula now for this example.

I'm going to use index a match, but if you'd rather use the look up or even look up, then please feel

free.

So we're going to do index array.

So what are we looking for here?

We're looking for the bib number.

So our array is going to be the bib number column before to be 27 comma.

We now need to find the row number.

So we're going to go straight in with our match because we're going to match the athlete name.

That's going to be our look up value.

We want to match the athlete name in the athlete name, column control, shift down arrow comma.

We want to do an exact match of the athlete name, plans of match clothes of index and enter.

Now it says an a in there.

And if you remember, I said I'd like you to add some error checking into these formulas.

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

this in an if and A..

And if we do come across Sunday, we wanted to say not found like so.

So now if I select an athlete, I should find that it either tells me what their bib number is or if

it can't find it in the table it's going to come up with not found.

Let's complete our other two index and match formulas, so equals index.

This time we're looking for the root.

So that is going to be our array control shift down arrow comma.

We automate the finding of the row number by using match and look up.

Value is whatever we have in H three.

We're looking up the athlete name in the athlete name column and we want to do an exact match, close

of match, close of index enter.

And remember, you could also add your error checking into this formula.

Let's do the final one.

This time I'm going to switch things up and I'm going to use a V lookup.

This time I need my look up value, which I'm going to find in Self H three, my table array.

Well, this is my table right over here.

I'm going to select everything.

Which column do I want to return what I want to return the position and counting from left to right.

I can see that this is column number five.

What type of match do I want to do?

Well, I want to do an exact match, so we want a false argument on the end.

Close the bracket, hit enter and I get my result.

Let's double check to make sure this is correct.

So let's find Donald Holland.

His bib number is one zero nine zero.

He ran the East Loop route and he finished in position number seven.