Tip:
Highlight text to annotate it
X
The VLOOKUP function in Excel allows you to vertically look down a list and depending
on what you are looking for, bring through a result. There are two types of VLOOKUPS
- an exact match which is covered in another video and an approximate match which we are
covering now. In this example here I have got some students and the results of their
tests. And what I want to do is pull through the symbol. Now you will see here I have got
a symbol table, so I need Excel to be able to look through it and decide which of these
symbols match the results. Now computers generally work on needing an exact match, so normally
what you would require is if it was looking for 75.3, it would need to see 75.3 somewhere
here to be able to say yes, I have found it. VLOOKUP is one of those functions which allows
you to get the computer to approximate the answer. If we did this manually on a piece
of paper, without the help of a computer, lets say this one here, I'm looking for 68%,
the way I would do it, is I'd say Ok I'm looking for 68% - where is the table? - here it is
- I know my number is bigger than that, it's bigger than that, it's bigger than that, it's
bigger than that - well, I've gone too far, so I'm guessing that that is the symbol. This
same methodology is what Excel uses in the VLOOKUP.
So let's now code the VLOOKUP based on what we understand. We go to the Function Wizard,
go the LOOKUP in Reference on VLOOKUP and now it is just a case of going through the
various steps. The first part is what must Excel look up? I'm going to say please go
and look up whatever is in that cell. The next thing it asks is where must it go look
it up? Remember Excel has got thousands of columns, a million rows and it needs to know
where it needs to look. I'm going to say please go and look here and the important thing about
Excel is the first column you highlight must be the one it looks up in, but you must then
highlight any columns that you may want to include. I am just going to put the dollar
signs on. The next item is what column index number must it pull through, so once it is
gone and found where this belongs, must it pull through column number one or column number
two. So we are going to say column number two. The most important part about this, though,
is the last item here the range look up. Because what the range lookup is doing is saying its
a logical value. If we want to find the closest match in the first column, sorted in ascending
order, we can either type TRUE or omit it. If we want to find an exact match we put FALSE.
So in this case we know that numbers such as sub .3, 68% they are not shown here. So
asking to find an exact match is going to give us an error. So we now know that we can
either leave it blank, but I'd rather you typed TRUE and when I say OK, you'll notice
that Excel has gone, found it and brought through the symbol A. You can now copy it
down and you'll see that all the symbols have come through based on a result and this table.
I just want to emphasise the importance of the sort order here. If you look carefully
when you looked at the range lookup - it is very specific - it says it is a logical value.
To find the closest match in the first column sorted in ascending order. That's very important
because the way Excel works when its doing an approximate match, is it takes the number
it is looking for - the 68% - starts at the top of the table and works its way down. So
it says is my number bigger than this- yes - go to the next one - is it bigger than that
- yes - and so it keeps going until it finds a number that is bigger than what it is looking
for. In that case it drops one back and that is now going to be the row it looks at and
depending on what column you are asking for, it will bring the item through. So this short
order is critical - if you get this short order wrong, Excel is going to make a mistake
when it tries to guess which is the correct number.