The Excel VLOOKUP function retrieves data from a particular column in a table based on a search term in another column.
Column index number, [
|1||Lookup value||Value to search for (must be in first column of the table).|
|2||Table array||The table to look in.|
|3||Column index number||How many columns away the returned value is from the lookup value.|
|4||Range lookup||Approximate (True) or precise (False).|
Suppose you want to look up the scores of two students. You have a table containing the scores of all the students of the course on another worksheet. But you don’t want to look them up yourself, you want Excel to do it for you.
Use the VLOOKUP function to search for the scores of the two students.
=VLOOKUP(A2, ScoreSheet!A1:B19, 2, FALSE) in cell B2 to look up the score of Alice.
Lookup valueA2 contains the value we are looking for: Alice.
Table arrayScoreSheet!A1:B19 is the table in the range A1:B19 on the ScoreSheet worksheet.
Column index number2 is the column in the table that contains the value we are interested in. Column 1 is the name, column 2 is the score.
Range lookupFALSE is because we are looking for the exact name match. Using false by default is a good rule of thumb.
Range lookupvariable a good rule of thumb?
Because almost always, we want to look for exactly the thing we have as our reference. Looking for almost the value can lead to unexpected results. Read the answer below to see when you would want to use TRUE for this.
When the table you are using VLOOKUP on has values that correspond to ranges of numbers. For example:
0 – 10 Low,
10 – 20 Medium,
20 – 30 High.
If you have value 15, the result should be Medium. In this scenario, a vlookup with value 15 will return Medium if you set Range lookup to TRUE but not if it’s set to FALSE.
If the lookup value doesn’t exist, Excel will show a #N/A error in the cell where the Vlookup function is used. You can ‘catch’ this error using for example the IFERROR function.
No, Vlookup is not case sensitive. So if your lookup value is MICHAEL you will also find Michael, mICHAEL and michael.
The VLOOKUP function has some drawbacks:
- The Vlookup function will only look for the lookup value in the leftmost column of the lookup table.
But what if you want to look up a value in the second column? You will have to reposition the table array argument to have its leftmost column one position further.
- The Vlookup function will only look to the right of the lookup value in the table. You will have to change the table layout if you want to look to the left of that column. You can use the Index and Match function as a workaround.
- The default value for
Range lookupis an approximate match. Even though most people will want to use the exact match instead. So the fourth argument should almost always be FALSE.
- Vlookup cannot search for values back-to-front, only front-to-back. If you do want this, you need to reorder your data.
- Whenever you insert or delete a column in the
Table array, the
Column index numbermay not be correct anymore. So you have to keep checking this argument to see if it’s still correct.
Quite a few drawbacks, huh? If you’d like to know about an alternative to Vlookup, check out this post on the problems with Vlookup and the all new alternative, Xlookup.