The Excel VLOOKUP formula is my favourite! Perhaps because it was one of the first formulas I mastered. It gave me an insight into the power of Excel and how it could help me in my job.
Interestingly, there are two ways you can use the VLOOKUP function; exact match and approximate match. However, I find that most people know one way or the other, and only a few know both.
Excel VLOOKUP Formula Video Tutorial
Download the Workbook
Enter your email address below to download the sample workbook.
Excel VLOOKUP Formula Written Example
Using the example data below, I want to lookup the commission rate in column I of the table that's in columns H:J, for the salesperson listed in column C, and put the result in column E.
Excel VLOOKUP Function Syntax
Before we get started, let's understand the VLOOKUP function's syntax.
=VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)
And to translate it into English it would read:
=VLOOKUP(find this value, in that table, return the value in the nth column of the table, but only return a result if you can match the value exactly*)
* for an exact match you must specify FALSE or 0 in the last argument of the VLOOKUP formula.
Let’s make it even clearer by applying it to our example:
=VLOOKUP(find the name Doug from cell C17, in the Commission Rates table H17:J24, return the value in column 2 of the table, but only return a value if you find the exact name "Doug" in the Commission Rates table, otherwise give me an error)
Rules, Common Mistakes and Troubleshooting!
- ‘Return the value in column 2 of the table’ is referring to the column number in the table H17:J24, not the column number of the spreadsheet. The information we want returned is the percentage rate, and it is in the second column of the Commission Rates table.
- ‘Only return a result if you can match the value exactly’ is telling Excel that we only want information returned if it matches our criteria exactly. i.e. Find Doug in our Commission Rates Table, and if you can’t find Doug, give me an error. The error displayed will be #N/A.
- VLOOKUP formulas read from left to right. You must have the information you are looking up (in our example the salesperson's name), in the first column of the lookup_array range.
- Lookup Table Location: The ‘Table’ you are looking up can be in the same spreadsheet. Or a different sheet in the same workbook. Or in a different workbook altogether.
- Sort Order: The table doesn’t have to be sorted in any particular order when using the Exact Match version of the formula, but you must not have duplicates. Unless the information on each duplicate is exactly the same. For example, if Doug appeared twice in our Commission Rates table with different percentage rates for each instance, VLOOKUP would return the rate on the first instance of Doug.
- VLOOKUP isn't case sensitive, so 'Doug' could be 'doug' or 'DOUG' or 'Doug', in either column C or the Commission Rates table.
When VLOOKUP formulas return #N/A errors it means Excel can't find the value you're trying to look up in your table. If you get this, but you can ‘plain as day’ see it's there in the table, then it’s likely you’ve got one of the values prefixed with an apostrophe. To check this go to each cell you're referencing and look in the formula bar and see if there is an apostrophe in either cell. You can only see the apostrophe from the formula bar. See example below.
Text Formats: Excel reads text prefixed with an apostrophe different to text without. Even though on the face of the spreadsheet they might look the same. You need to make sure both the value you're looking up, and the value in the table either both have the apostrophe, or both don't. The quickest way to get rid of the apostrophes is to do ‘Text to Columns’. Or run it through the VALUE function, which converts numbers formatted as text to actual numbers.
- Leading or Trailing spaces are another cause of VLOOKUP errors. Edit the cells to check if there are extra spaces before or after the lookup value, or the data in the first column of the lookup_array. Use the TRIM function to easily clean leading and trailing spaces.
- Dynamic Column References: Use COLUMNS or the MATCH function to dynamically find the col_index_num argument for VLOOKUP. See the video above for step by step instructions.
Excel VLOOKUP Approximate Match Formula
Now you've mastered the VLOOKUP exact match formula, you're ready to learn VLOOKUP approximate match. This technique is lesser known and many people make the mistake of using a nested IF formula instead of this simple approach.