Jun
05
2011

Vlookup Help

MS Excel is by far the best tool for the average user to be able to manage data.  One of the most useful functions is the VLOOKUP Function:  a special look up function that searches for key data in a table based on your desired parameters. This is invaluable when your table has multiple adjacent data in different columns that are directly related to them, and you need to look for specific information. For example:

Here is an example of a data set arranged in a table.  In this example, it’s information about students.  So far, it is easy to see the student number, name, year level and age. The problem arises if there’s more information added (i.e. address, cellphone number, etc.).  Currently, to find that information, you’d have to spend time going to another workbooks or worksheet.  Finding your key information would be like “finding a needle in a digital haystack”.

Definition:

VLOOKUP stands for Vertical Look Up, as such; it will track the data vertically. Function-wise, it searches the leftmost column of the selected group of cells and returns the value of the cell in a certain row from that same group where you choose what column the data will be obtained. What this means is that, from the example above, you input the student number and it will collect data on that specific row of the inputted number. This will be explained later on.

*There is also a similar function called HLOOKUP, but works on rows rather than columns. We won’t discuss much on this but the idea and functions are very similar to the VLOOKUP.

Syntax:

The syntax for the VLOOKUP is

=Vlookup(lookup_value,table_array,col_index_num,[rangelookup])

The explanation for each argument is as follow:

  • Lookup_value – (or WHAT do we need to look up?).  This is a required argument (not placing anything here will result in an error). This is where you need to place the value you are looking up. It can be a number, a cell or a character, though the latter may require the use of quotation marks (“ “). The function will always look for the stated value on the left most column of the data set. For example, if the selected data set above covers A2:D18, then it will look for the value in column A; if you set it to B2:D18, it will look for the value in column B.
  • Table_array – (helps to answer WHERE are we looking it up.  More specifically, which data set?).  Another required argument. This is where the data set is being inputted. Like what was mentioned in the lookup_value, the range of data selected will be the table array and the leftmost column will be the basis for the lookup_value.
  • Col_index_num  (In the range above, Which column should we return the value of?)– (Another required argument. This argument will return the data of a specific column that is defined by the lookup_value. The values that can only be inputted here is 1 up to the number of columns on the data set indicated by the table_array. If I use the same example as above and placed 2, it will return me the name (as it is the 2nd column of the data set); if I inputted 3, then it will show the Year Level. If you do change the data set, let’s say B2:D18: then 1 becomes the name, 2 becomes the year, and so on and so forth. Be wary on what the range of the selected cells are and adjust accordingly.
  • Range_lookup – An optional argument; you can choose if you want to input a variable here, but it will have a default value of TRUE if you do not input anything. This argument will specify if you want the VLOOKUP to find an exact or appropriate match. If you chose TRUE as the value, it will search for an exact or appropriate (or closest) match. If the lookup_value is larger than any of the data in the lookup_value column, it will choose the next largest value on that column. On the other hand, if you choose the FALSE value, it will only search for an exact match. If there are 2 or more of the values that are an exact match, it will use the 1st value. But if there are no values that match, it will return an error.  In almost every case I have used this function, I have used True.

Let us say that I want to find the student name of Student # 45, so rather than doing it manually, I use VLOOKUP at cell A20 like this:

I placed 2 pictures to let you see how the arguments work. The 1st picture shows how the arguments are to be written and arranged. “45” is the lookup_value, “A2:D18” is the table_array, “2” is the col_index_number, and “1” is the range_lookup. On the 2nd picture, I marked where the arguments will be focused on. The lookup_value is marked in red, as you can see; it’s set to locate “45”. The green box is the table_array, while the blue box is the col_index_number. Notice that out of 4 columns, I use the number 2 so it will look for the corresponding data on the 2nd column. The range_lookup doesn’t show much on this table, as it will just determine if the lookup_value is an exact or appropriate match. As such, connecting all the boxes together will show you that the answer is “Yolanda Richards”.

 

Assume that I placed the value “55” on the lookup_value, but you noticed that there is no 55 on the table. On the 1st picture shown below, the range_lookup value is TRUE, and thus shows the closest match, which is “54” and returns the value “Yuri Ona”. On the other hand, I placed FALSE on the range_lookup in the 2nd picture. Instead of returning the closest match, it will show an error because it cannot find the value “55” (because it doesn’t exist in the table).

 

It’s that simple! Try it.

Common Errors:

Despite how easy to use VLOOKUP is, it is likely that you may make a mistake on the input of values on the arguments, and thus will return an error or incorrect data. Here are some of the common mistakes.

  • Col_index_num can only have a value between 1 and the total number of columns. Anything else will result in an error.
  • Sometimes, using the absolute cell value (i.e. $C$2) may help if you are copying the function into another cell (More so if you drag the cell down to copy). Relative cell value (without the $) will change the table_array according to where you will copy the function to, and will give you a different answer than you would expect.
  • The TRUE value for the range_lookup can give an incorrect answer if the 1st column of the table_array is not sorted in an ascending order. Remember that the TRUE value will locate the next closest value if the lookup_value is not found and will chose a different value than you would expect (though the wrong answer can also happen if for example, the highest value of your 1st column is found in the middle of the column), hence is the reason why it has to be sorted.  This is only an issue if your data set has multiple entries for the same row title.

Examples and possible uses:

Remember that you can also use VLOOKUP in conjunction with other formulas & functions, such as IF. You should experiment and see which ones would work best for you.

Example 1:

I added a grading score that ranges from 0 to 100 (with 100 being the highest). Now, suppose that student number 53 had a previous project done and was promised extra credit on his/her grade, but we wanted to keep a record of his real grade on the assignment (excluding extra credit).  Instead of looking for the number and editing the score in the exact cell, I’ll do this with VLOOKUP:

The formula shows the VLOOKUP function with the arguments (53,A2:E18,5) but added a +50 (We’re so generous, aren’t we?). As you have noticed, the col_index_number is 5, which is the grade column. The VLOOKUP should return the value “40” but since I adjusted the formula to add 50 pts in, thus the answer is 90.

Example 2:

Supposed I want to see if a single student  passes or fails, because I need to write on their report card if they do pass or fail, based on their student number alone.  I’ll do this:

You can see that I made an IF function as stated:

=IF(VLOOKUP(A20,A2:E18,5)>=80,”Pass”,”Fail”

This formula shows that the VLOOKUP will search for the value that I will place in Cell A20 (for which there isn’t at the moment, thus comes out as an error) and from the range of cells, will return the value from the grade column (5). The IF function now, will use the value to see if the grade is greater or equal to 80, if the answer is true, it will show “Pass”; but if it is not true, then “Fail” is the other answer.

 

So I place the number 46 in Cell A20, the formula begins to check if the student pass or fails. As you can see, the student’s grade is 88, which is greater than 80. In our example, 80 is the pass rate.   Therefore, the formula sees that as true, and returns with the answer as “Pass”.

This time, I place the number 50 on Cell A20, and the formula begins to check if the student pass or fails.  Unfortunately, student 50’s grade was 79, which is less than 80. Thus, the formula sees that as false, and returns with the answer as “Fail”.

Closing notes:

In this example we only looked at data on the same tab, for illustration purposes.  The vast majority of the times we’ll use vlookups out in the wild will be mashing datasets up together, using multiple tabs.  So If sheet 1 (names) just has a list of names, and sheet 2 has a list of names and addresses, and sheet 3 has a list of names and phone numbers, we could use the power of vlookup to consolidate them all into one tab, quite easily.   I’ll leave you to figure out the syntax (hint: use Excel’s formula builder).  Using Vlookup on multiple tabss will allow you to untap a lot of Excel’s potential.

4 Responses to “Vlookup Help”

  1. NOW I get it! Thanks for the explanation – the true/false part was giving me a lot of trouble, but now I know to just not even bother with a ‘true’ value.

  2. Good vlookup guide. Bookmarked for next time my coworker leeves and I dont remember which column number to use

  3. Glad I could help!

  4. God help me, I put aside a whole afternoon to figure this out.

Leave a Reply