Home // Video Tutorials // 3 - 6 (Intermediate) // 5 // Vlookup Exact Match

Vlookup Exact Match

How to use Vlookup Exact match in Excel to extract information out of lists of data or a database.

 

Vlookup Exact Match

The VLOOKUP Exact match function in Excel is one of the most important functions to get to know. The V stands for vertical and it allows you to look up a data base and bring back relevant information. Before we look at how the function works, let’s just think how our brain would handle it.

So if I gave you this same example on a piece of paper and I said to you, please over here write the percentage of this student. in all likelihood this is what your brain would be doing, what your eyes would do- you’d look here -see what student you are looking for – you’d then look around this spreadsheet, find a table that is applicable, find the relevant column, look down until you find the one you want. And once you have found the one you want, you’ll look to the right and pull through, in this case, the result or the symbol.

This is exactly what we need to tell Excel to do. But now we need to explain it in terms that Excel understands. So lets create the formula – I click in that cell, click on the Function Wizard, and you’ll see under the category Lookup in Reference – right at the bottom we have something called VLOOKUP. When I click OK it gives me the following options. Now if you look at what VLOOKUP is looking for, you’ll see that is very similar to how we would do it if we were doing it on a piece of paper. so the first thing it asks for is the Lookup value – so what it is saying is – in this cell where must I go look – what must I look for? In this case it is as simple as saying please go look at that cell there.

The next thing it asks is where must I go find this relevant information? Now remember Excel has 16000 columns and over a million rows. This information in this table could be anywhere so you have to specify to Excel exactly where it is – which bits of information must it look for? So in this case I am going to highlight it over here. One of the things to know about VLOOKUP is that you have to include all the areas that might be involved in this calculation, and the very first column of the highlighted area must be where it must go and look for the lookup value. Once we have got that, I am just going to put dollar signs on.

The next thing it asks for, is Ok it has now looked for this student, in this column, found it, and now it wants to know which column index must it pull through? So must it pull through that same column, must it perhaps pull through that one or this one? The way you represent it in Excel is with a number – so if I put a 1 – it means bring through the information from here. So to go, look it up, find it and bring it back. In this case, I’m actually going to put a 2 – because what I’m saying is please find that student in this list, and once you find him go across 1 to column no 2 and give me the answer.

The most important thing to realise about VLOOKUP, though, is that you have to get this part right – the range lookup. And what you’ll see it says is a logical value. If you want to find the closest match in the first column, sorted in ascending order – you put a TRUE or omit it. If you want to find an exact match, we put FALSE. Now generally most people just put FALSE without understanding why, but what we are saying is if I’m looking for the result of student A111, I don’t want Excel to kind of guess one of these numbers – it must go and find exactly student A111. So what I am going to do, is I’m going to put FALSE here or you could have put a zero. And when I click OK, you’ll see it brings through 34%, A111 – is that one there – there’s the 34%.

We can do the same thing for the symbol, so now I’m going to go here and I’ll go a bit quicker – go to the Function Wizard – go to VLOOKUP. So what are we looking up? We are looking up student A111. Where must Excel go to look it up? It must go look in this column here and we are going to include all the columns we may end up using – I have to put my dollar signs on. When it finds what it is looking for, what must it bring back? I want it to bring back the third column – 1 , 2, 3 and most importantly, I am going to put FALSE here, because I want it to find an exact match – it must find exactly that student number. When I click OK, it brings through the symbol E – which is correct. OK so now we can go and copy this down – I am just going to copy it down and suddenly we have got an ERROR message. Excel is saying that it is not applicable. One of the uses and benefits of VLOOKUP, is that it will tell you in this scenario – where it cannot find something. So you’ll see if we look at what we are looking at – we are looking at cell A15 – it is looking for the student A115 – again in our table but let’s look here and you can see there is no A115. So what Excel has done it has told you it is not applicable – it cannot find the answer – and that in itself is useful information because occasionally you want to know where are items that cannot be found.