Home // Video Tutorials // 3 - 6 (Intermediate) // 4 // Excel VLOOKUP function

Excel VLOOKUP function

How to use the Excel VLOOKUP function to vertically look up data in a list and return associated data.

 

Excel VLOOKUP function

The Excel VLOOKUP function allows you to look up information from a table of data. In this segment you will learn how to find and activate the function and how to use the function in its two forms to find an exact match and an approximate match.

What you have here is some student numbers and results from a test and what you would like is to put the symbol that relates to the result. We have a symbol table here which indicates what scores generate what symbols. In order to do this using VLOOKUP, you do the following.

Click on the cell where the answer needs to be put. Activate the Function Wizard and find the VLOOKUP function which will be in Lookup and Reference. Because of the V it is right at the bottom.  Press OK. And now you have to fill in the criteria. The first one they ask for is the lookup value and this is the value that Excel has been told to lookup in the database. And as you will see it says whatever this value is it needs to be in the first column in the database you are going to use which is here. So in this case we want to look up that value.

The table array is the table containing the text numbers or values which need to be retrieved. In that case it is here and you have to highlight the entire section. So in this case you would highlight it like that. Now it is important to realize that because you might copy paste this formula it is very useful to anchor this by making it absolute. So what you need to do is put the dollar signs in there.

The next criteria is asking you, when it finds the number you are looking for what column must it pull through. In  terms of numbering this is considered column 1, this is 2, that would be 3 etc. In this case we want it to pull through number 2.

The last criteria is it asks you what or how do you want me to look this up. Must I find it as an approximate match  or must I find an exact match. Now because of the nature of this information, the results can be anything, it could be 75.3, it could be any number so it is useful to put these numbers into bands and assign a symbol and therefore it is an approximate. In that case it indicates that you must either type TRUE or omit it. We will type true and when you press OK you will see that the symbol is pulled through. What Excel is doing its taking the 75% looking here and saying, no 75% is higher than that, going to the next one, looking and saying no no its higher and so it continues until it hits the number which represents the number being pulled and then it pulls through the information that you requested. And now it is possible to copy and paste this so that it automatically pulls through all the relevant information.

Now that you have sorted out the symbols that are related to the results you want to build a little query tool where, by typing in the student number, you will get the percentage and the symbol with the correct person. Now because the student numbers are not in ascending order and because they are unique this is a situation where you might want an exact match.

In order to use VLOOKUP in this situation, you click on that cell, activate the Function Wizard again  and you go through the process again. The lookup value is the number you want it to look up. In this case it is that cell there. The table array. The table is now this table here and because you are eventually going to look up the symbol as well, you highlight the entire area and again I cannot emphasis enough, I suggest you anchor it  by making it absolute. The column index number. In this case you are looking for the percentages. That’s the first column, so its the second column again so I put a 2. And now you come to the range lookup, and as indicated earlier if you put a TRUE or you omit it its going to find an approximate match so it will guess in which area this number sits and give you a number. We know that that needs to be exact and so what we want to do is type in the word FALSE. Click OK.

And what you will see is it looks up that number and finds the relevant percentage. So if we typed for example ‘A120’ this will change to reflect that new number. And if we want to pull through the symbol as well we just go through the exact same process. The lookup value again will be here, the table array as indicated, anchor it, the column index number in this case however is actually the third column, so you type in your 3. The range lookup is false. And you push OK. And now both will be pulled through. So now whatever student number you request the correct result and symbol will be pulled through.

If for whatever reason you type in a number here that does not exist in the database. So for example  we put in ‘A101’  an error message (#N/A ) will be generated and that’s basically Excel telling you that it cannot find an exact match. Although this could be a problem sometimes  it is better to find out that you made a mistake here then to have the problem where it tries to guess the approximate number and you are never actually sure whether the correct information is being pulled through.