How to use the VLOOKUP approximate match to find matches within bands in a list.
NEW: Go to VLOOKUP Explanations with downloadable exercises and detailed solutions
Vlookup Approximate Match
The VLOOKUP Approximate match 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.