Hello and welcome to mr Tompkins edtech, and my series “a Teachers guide to Microsoft Excel”.
In this video we are going to be looking at Tip 2 -the VLOOKUP function,
which is a very powerful tool for combining two data sets together. If you missed the
series intro and Tip 1 on Naming Ranges, you might like to go watch those videos first,
which will explain a bit more about this series and will also tell you where you can down load the
excel workbook that accompanies this series. If you’ve already done that, then let’s get into it.
Now Microsoft have recently added a new function called XLOOKUP, which in some ways is more
versatile then the older VLOOKUP function, and we will be looking at this in depth in the next video
in this series. However, I still think VLOOKUP is a handy function to know in its own right, and you
will find it in a lots of spreadsheets that were designed and built before Office 2019 came along.
So lets take a look at what it does. In the last video, we named this table of information as
“students”, so now if I type “students” into the name box up here, then you can
see excel’s focus jumps onto this table. Notice that the first column of this table
is called ADNO and contains a number. ADNO is short for “admission number” and my school uses
this number to uniquely identify students at the school. Students are assigned this
number when they first arrive at the school, and will hang on to it until they leave.
Imagine we wanted to know the name of the student with an ADNO of 1003.
Now if I asked you to do it – you would most likely scan down the first column
until you found the number 1003. Then you would read across to the name column and
there would be the answer - “Buzz”. Now the VLOOKUP function does this exact same thing.
If I type in =VLOOKUP(1003, students, 2, false), Excel returns the value “Buzz”.
Let’s break down the function. This function needs 4 input values. = VLOOKUP(value to find, range
containing data, column to read off, True/False) The first is the value you are looking up – in
this example the number 1003. The second value is the data range containing the
information we are looking up – in this case the named range “students” that we
already defined in the last video. The 3rd value is the column we want
to read across to – so here the names are stored in the second column, hence the 2.
The 4th and last input value needs to be either true or false. This true/false flag indicates
whether you want an exact match (false) or the closest match (true). Mostly for student data,
we want an exact match, so usually we use false. Let’s try a few more examples…
If I want the tutor group of student 1002, tutor group is the 5th column, so that’s going to be
=VLOOKUP(1002,students,5,false) Gender of student 1003,
gender is the 4th column, so that: =VLOOKUP(1003,students,4,false)
Now instead of an actual lookup value, you can include a cell reference,
so if I want to look up the surname of the student id stored in cell B14, that’s
=VLOOKUP(B14,students,3,false) Now it can be very useful to write them
with a cell reference like this, because you can then use the “fill-down” feature to copy a similar
formula into the cell below. So If I select J14, and then drag down this little square in the
bottom right corner of the cell marker, its going to copy the same formula into the cell below, but
notice its changed B14 to B15, returning the value “Inboots”. This is an example of what’s called
a relative cell reference, when excel copies a formula from one place to another, then any cell
references in the formula get changed depending on how many rows or columns you move it by.
We’ve copied it one row down so the row reference has changed by one row – B14 to B15.
Ok, it’s time for you to practice this a little bit before we carry on. So pause the video now,
and open the accompanying workbook, link down below, and open worksheet “Tip2 – vlookup”
and complete task 2a. Once you have finished with the task, come back and press play again.
Ok, welcome back. If you managed to get these results for Task 2a,
good job! If not, have another go. Once you are done, remember to tick the box below
to indicate you have finished this task and we will move on.
Now I mentioned that this ADNO is a unique student identifier used in our school. Most
reports and data sheets that the school creates on students will usually include this code somewhere,
and usually at the front of the data sheet. Now this is extremely useful, as it means
you can merge data from one or more of these various reports into your own gradebook using
the VLOOKUP function, as long as you too include this same unique identifier in your own gradebook.
So I’m going to show you how to do this. This second table here I’ve already named
“progress”, so if I select “progress” from the name box pull down list,
its going to refocus excel onto this table. So now I have two named ranges, “students”
and “progress” and notice that both tables have ADNO included as their first column.
Now I’m going to make a new table that brings together data from
both the students table and the progress table. I’ve started off by listing all the ADNOs in the
first column – I could just copy/paste that over from either of the tables.
Now the rest of the information I’m going to generate with VLOOKUPs. Ok, lets go.
First up, surname. Now surname is the 3rd column of the students table, so here I’m
going to type =VLOOKUP(D49,students,3,FALSE) and you can see once I press enter, that gives
me the value “Duck”. Next column is name. Now that’s in the 2nd column of of student table,
so lets type =VLOOKUP(D49,students,2,FALSE) The 2018 data is in the 2nd column of the
progress table, so =VLOOKUP(D49,progress,2,FALSE) Similarly 2019 and 2020 are in the 3rd and 4th
column of the progress table, so =VLOOKUP(D49,progress,3,FALSE) and
=VLOOKUP(D49,progress,4,FALSE). OK, now because I used relative
cell reference for each of the formula I entered here, I can simply select the whole first row,
and then use this little square on the bottom right of the selected range, and drag that to
fill down this formula to the bottom of the table. And voila! We have a completed table,
with data pulled in from two separate tables. And this is the real power of the VLOOKUP
function. It makes it really quick to pull data together from two separate spreadsheets.
It also does this very reliably. If you have ever tried to merge data together from two
spreadsheets manually by cutting and pasting, nearly always something goes wrong because the
students were in the wrong order, or you’ve lost one or gained another one from somewhere,
and you end up having to work out where it all went wonky and try and put it right.
With VLOOKUP, you don’t have to worry about this – its going to look every value up precisely.
If it does struggle to find what it’s looking for, because you ask it to look up a value that
doesn’t exist, you will get an #N/A error which stands for “not available”. I’ll show
you how you can deal with these sorts of errors in Tip 9 later in the series.
For now, I’d like you go and complete task 2b in the accompanying workbook,
where you will need to pull data together from 3 different tables, “students”,
“behaviour” and “progress”. So pause this video and go do it now. Once you’re done,
come back and press play again and we’ll check your results and wrap things up.
Ok, hopefully you managed to complete the first row and then successfully used fill-down
to fill in the rest of the table. So it should now look like this:
If not, have another go. Once you are done,
remember to tick the box below to indicate you have finished this task.
So I hope these two tasks helped you to understand the VLOOKUP function, and gain an appreciation of
how useful it can be to bring in data from another spreadsheet into your gradebook.
Now I mentioned before that combining data this way is a lot more reliable
than trying to cut/paste data from one book to another. The other major benefit
is that if I now go back to one of tables I used in my vlookup formula,
so for example maybe I’ve spelt Puss’s surname incorrectly and go back and add a hyphen,
then that change is automatically going to update any other cells that are looking up data from it,
so you can see its changed here and down here too without me having to do anything extra.
So for a casual user of vlookups, that’s all you need to know about it.
Now for more advanced users, if you are using vlookups to pull in data into your gradebook
from one or more data tables stored in different workbooks then listen to this next bit carefully.
If the original data resides in a different workbook, excel will still try to maintain
the links between these two workbooks. So if you change a value in a table in workbook A,
then your vlookup formula in Workbook B that uses that table will also update.When you open Workbook
B, you may get a pop-up asking whether you want to update your links or not. So saying yes here
will check any vlookups you have and update them from the source if there have been any changes.
If you say no, its going to keep the existing value it had the last time you opened it.
You can go to the data ribbon and open “edit links” to see
all your workbooks existing links and manage these connections.
Update values will go check for updates and change any cells necessary. “Change source” allows you
to point the link at a different workbook. So maybe if you changed the name of the source,
or saved a newer version with a different name, you can point the link at that new file instead.
Break link replaces any dynamic links with static data, so if I break the link to workbook A, then
my vlookup formula in workbook B is going to be replaced with the value it had at the time of the
link. This can be useful to do if you know that you are not ever going to update the source file,
so its not worth excel trying to maintain this link. So best to break these links
and replace your dynamic vlookups formula with the static values. I hope that all made sense.
Ok so we now know how to create a vlookup formula,
to pull in data from another table which can be somewhere else in our workbook
or in an entirely different workbook. We’ve talked about the importance of including a
unique student identifier which is commonly used by your school to enable you to do this
accurately. We’ve looked at using relative cell references to quickly fill-down these formulas
to populate our entire gradebook with data from another table in another spreadsheet. Finally we
took a quick look at the “edit links” tool, used for maintaining these links with other workbooks.
In the next video, I will be taking a look at using excel to convert between things like
levels, marks and grades. We’ll be taking a look at the brand new XLOOKUP function
and how it differs from VLOOKUP, and when we might want to use it instead.
If you liked this video, please do let me know by clicking on the like button below.
Also why not subscribe, which just means that YouTube is going to be more likely
to recommend my videos to you in future. Click over here to start the next video in
this excel series. Or click the thumbnail below for the full series playlist.
You might also like this other great edtech tutorial down here. See you on the next video.