In this section of the course, we're going to start taking a look at some of the most useful intermediate
level functions and formulas in Excel.
And the idea behind this section is really to give you a taster of the types of things that you can
do with more advanced formulas to move your knowledge on a little bit from those basic formulas that
we looked at earlier in the course and really set you up for success if you plan to do the Excel Intermediate
course.
And we're going to start out this section by taking a look at one of the most popular functions, and
that is the the look up function.
Now, Vila Carp is classified as a look up and reference formula in Excel.
And if we jump across to the formulas ribbon, you're going to find dead underneath the look up a reference
section all the way down at the bottom.
There it is.
Vila Carp.
Now, if we take a look at this screen, tap because this is going to tell us exactly what Vila Carp
does.
So it's going to look for a value in the leftmost column of a table, and then it returns a value in
the same row from a column, you specify.
So what exactly does that mean in terms of data?
Well, let's take a look at the example that we're going to use now in this worksheet.
I have a reasonably large dataset and this is related to movies.
So in column eight, we have the title of the movie in Column B. We have the year that it was released
in Column C. We have the certificate.
It's been assigned in Column D. The date that it was released in Column E. We have the runtime of the
duration of the movie and Column F. We have the genre that that movie belongs to.
And then finally, in Column G, we have the user rating and what we want to be able to do here is we
want to be able to type in the name of a film and have it return the year, the certificate, the genre
and the rating from the table.
And it's this kind of situation where we would use the V lookup function.
Now, the thing that a V look out relies on is a look up value.
So whatever it is that you're using to look up this information, in this case, the film name that
we have in J2 that must exist in the table.
So let's pull back the year using the V look up function.
Now I'm going to type equals into the cell and then I'm going to start to type V look up.
Notice it's come up in IntelliSense, so I can just press the tab key.
Now, let's take a look at the different arguments.
Remember, the arguments are what we need to complete in order for this formula to work.
Now, the first thing that this formula requires is the look up value.
So this is the piece of information that you're using to look up other pieces of information in the
table.
So for us, the look up value is going to be the film.
So whatever we have in Cell J to now remember, this can change.
I can type in all different kinds of movie names in here.
But regardless of what I type in because we're using a cell reference, it's always going to look up
based on the value in cell J2 comma to move on to the next argument.
We then need to provide the table array.
So effectively, this argument is asking us, where is our data?
What are we looking up this information in?
Well, for us, our table array is this data over here, so I'm going to select the entire table.
Now, let's jump up to the Formula Bar to carry on editing coma.
Now it's asking us for the column index number.
And this is a really important part of Vila Up.
The column index number is where you specify the column of information you want to return.
So what are we looking up here?
What is the result that we're expecting?
Well, we're trying to return the year of the film Moonlight's so we want to return the year.
Now we look up numbers columns from left to right.
So the title column is column one, the year column, column two, certificate column, column three,
so on and so forth.
So my third argument here is going to be two, because the year is column two in our table array comma.
Now our final argument here is an optional argument.
How do I know it's an optional argument?
Well, if I take a look at the argument, you can see there has square brackets around it.
Now, any argument that you see in square brackets means that it's optional, so you don't have to input
anything for this.
But if I do press comma, you can see the arguments that we have.
True or false?
So what am I doing here?
Am I doing an exact match or an approximate match?
And this is related to the lookup value, the film that we're using to look up the data?
Well, I want to exactly match the name moonlight in the table.
So this argument is going to be false, and I would say that the false argument on the end of a V lookup
is the most common argument.
Now, if you don't put anything on the end here, remember this is optional.
It's going to do the default, which is to do a false.
So I'm just going to type false on the end and close off my bracket.
That is my VLA Cup formula.
Let's enter and see if this works.
There we go.
So it's pulled back the year 2016.
So if we look for moonlight in the table and is this one just here, you can see that yes, the year
that movie was released was 2016.
So we've used the film name as the look value in this table, and we've returned column two, which
is the year.
Let's do this again equals v look up Typekit look up value again is what we have in J2.
The table array is the same thing, so I'm going to control shift down to select everything.
Let's click back up in the Formula Bar comma.
The next thing we need here is the column index number.
So what piece of information do we want to return?
This time?
We want to return the certificate.
So counting from left to right, the certificate column is column number three, and we want to do an
exact match of the movie name in the table.
So our final argument here is going to be false.
But also remember, in Excel, you can type a zero to represent false and a one to represent true.
So I could put a zero on the end here or leave it blank.
Close the bracket and certificates are.
Let's double check that yes, it is now.
One way I could make this a little bit easier on myself so that I'm not having to go in and select this
cell range.
Each time for the table array is to turn this data into a table or a named range.
So I'm going to create a table control to click on.
OK, now my data is in a table and on the table design ribbon.
I'm going to give my table a name, so let's just call this movies and hit enter.
Now that I have this table array named, I can use it in my V lookup calculation.
So this time we're going to pull back the genre, and I can see that the genre is column number one
two three four five column number six, so equals v look up.
The look up value is the movie the table array.
This time I can use the table name, so if I start to type it in.
Notice it comes up underneath in IntelliSense tab to select Comma, the column index number.
I'm looking for the genre, which we've established is column number six, and I want to do an exact
match, so I'm just going to accept the default here.
I'm not even going to add that final argument.
Enter, and yes, that movie is in the genre of drama.
And just to show you another way that you can do this, you could also utilize the functions.
Dialog box.
So if we go up to the Formulas tab and click on insert function, I'm going to search for V look up.
Let's click on Go.
Double click to select.
And now I can complete my arguments in this way.
So the look up value is to.
The table array.
Is movies, the column index number?
Well, this time we want to return the rating, which is column number seven, and we want to do an
exact match, so we need a false argument on the end that let's click on.
OK, and there we go seven point five.
So now what this means is that every time I change the film or type a new film into here, all of these
results are going to update.
So if I type in Jackie and her answer, you can see that those results have changed to reflect this
movie in the table.
What about if I type in?
Let's go for La La Land to enter again at the top?
Those results have now changed.
So that is how you do a basic Vila cop using exact matching.