The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for an item given the ID of the product.
But VLOOKUP is more than just that; it is the gateway to real Excel knowledge. The VLOOKUP function contains everything that a function can throw at you: multiple required parameters, optional parameters with defaults, and needs both ranges and numeric data in its input strings. If you can master this function, you can master ANY other function in Excel.
The VLOOKUP function syntax is as follows:
And here’s what it means:
| Lookup_value: | The value you want to look up. This can be either a value, text string or a cell range (the cells’ value will be used). |
| Table_array: | The table you would like to look the value up in. Be aware that VLOOKUP will check the first column in the table looking for the value you have provided. |
| Col_index_num: | What column of the table (not the worksheet) you’d like to return the value from when a match is found. |
| [range_lookup]: | Would you like to return an approximate match (TRUE) or an exact match (FALSE)? An approximate match returns what Excel interprets as the closest result to what you have looked up. An exact match returns #N/A unless the exact thing you’re searching for can be found. The default is true (approximate) if you do not specifically declare this parameter. |
Have a look at the following spreadsheet:
Notice that we have a table of marginal tax rates here. Wouldn’t it be nice if we could feed the spreadsheet a tax balance, an income level and return the appropriate amount of tax to pay? Well, of course we can do this; accountants do it all the time. Here’s how we do it with VLOOKUP:
Build the following formulas into the worksheet (And hey! Do it in the example above, it's a live Excel worksheet right in this page!):
- F7 =VLOOKUP($F$5,$A$4:$C$10,1,TRUE)
- F8 =VLOOKUP($F$5,$A$4:$C$10,2,TRUE)
- F9 =VLOOKUP($F$5,$A$4:$C$10,3,TRUE)
- F11 =F9+(F5-F7)*F8
Notice the similarities and differences in the formulas:
- In each case are looking up $F$5 (in this case 10,000)
- All results are being looked up in the table covering $A$4:$C$10
- Each formula ends with TRUE, indicating that we want the closest match
- Only the third argument is different!
The third argument tells us which column we want the value returned from for the match in column A. So in the first case, we find 10,000 on row 6, and we want the value from the first column in that row (i.e. 10,000). In the second formula, we’re looking at the next column over (column 2), which contains 10%. And naturally the third column contains 0.
Try changing the value in F5 to 9,000 and you’ll see that all of the results change to 0. Why?
With the TRUE argument at the end of the VLOOKUP function, the approximate match is discerned as the closest value without going over. (Kind of like the Price is Right!) In this case it makes sense, as there would be no tax payable if you made less than the 10,000 threshold. Change F5 to $15,000 thousand though, and you’ll see that it picks 10,000 as the base to look up.
This highlights and important point when using approximate matches: Make sure that your data table is sorted in ascending order or the value returned may not be correct!
One final word on using VLOOKUP to find an approximate match: you can omit the ,TRUE from the formula since it will default to TRUE. Don’t. Get in the habit of declaring the TRUE or FALSE so that you know your formula is doing what you intended.
Now that you’ve made the approximate match work, change the value in F5 back to 10,000, then modify each of the VLOOKUP formulas to use FALSE instead of TRUE for their final argument. If you did it correctly, you shouldn’t see any difference.
Now go to F5 and change the value to 9,000. All the VLOOKUPs recalculate with #N/A!
The benefit of FALSE is that it tells you when a true match cannot be found. While not helpful for a tax table like we have here, this can be very handy when working with product or member lists. Think about a scenario where you put in a customer number to get their A/R balance. In this case, you’d far rather have a #N/A show up in your cell than to give the customer the wrong balance!
VLOOKUP formulas can be tricky, especially when you are working with exact matches. If you’re having issues, review Microsoft’s quick reference card. It’s an excellent resource to help diagnose VLOOKUP errors!
And, of course, you can always ask in our forums as well!
This article was originally contributed to CMA Update Magazine - Spring 2011, and was republished here in anticipation of Mr Excel's:
