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.
Once you master VLOOKUP you'll find you have the occasion to use it all the time.
Interestingly, there are two ways you can use the VLOOKUP function, but I find that most people know one way or the other, and only a few know both.
In this article we’re going take a look at the VLOOKUP Exact Match version of the formula, but first let’s set the scene.
By the way, the other way is what I call the Sorted List VLOOKUP formula.
Excel VLOOKUP Formula Example
In the list below we want to calculate a commission in column F for each builder. However, each builder has a specific commission rate they are entitled to. Thankfully we have this information in a table to the right, and this is where we give VLOOKUP the opportunity to strut its stuff.
Excel VLOOKUP Function 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 column x of the table, but only return a result if you can match the value exactly)
Let’s make it even clearer by applying it to our example:
VLOOKUP(find the name Doug from cell B2, in the Commission Rates table H2:I9, 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)
- ‘Return the value in column 2 of the table’ is referring to the column number in the table H2:I9, 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.
On the other hand, if we told Excel it was ok to not find an exact match, it would return the next best result. i.e. If Doug wasn’t in our Table Excel would return the next best result. In this example we wouldn’t want it to do that, but this option is handy in other situations which we’ll cover in another tutorial.
OK, now that’s clarified, our formula in column F for the above example would be:
Note: Where ‘FALSE’ is telling Excel we want it to find an Exact Match only.
Our Excel table would then look like this with the VLOOKUP formula in column F:
You’ll notice in the formula bar above there are ‘$’ signs around the reference to the table H2:I9. This is called an absolute reference. Absolute references allow us to quickly copy the formula down column F without Excel dynamically updating the table range as we copy.
How can we make this VLOOKUP formula even better?
By calculating the commission $ amount in one step in column F. Let’s say commission is calculated as Total $k x Commission %, our formula in cell F2 would read:
And in seconds we can have hundreds of calculations done!
Rules, Common Mistakes and Troubleshooting!
- VLOOKUP formulas read from left to right. You must have the information you are looking up (in our example Doug in the Commission Rates Table), in a column to the left of the information you want returned. Which in our example is the ‘percentage rate’. i.e. it has to go ‘Doug’, then ‘% rate’. Excel wouldn’t be able to find it if it went ‘% Rate’ then ‘Doug’.
- You can have as many columns as you like in your Table, just so long as you follow the ‘left to right’ rule above.
- 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.
- The table doesn’t have to be sorted in any particular order, 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.
- The formula isn't case sensitive, so 'Doug' could be 'doug' or 'Doug', in either column B or the table.
- What does it mean when my VLOOKUP returns a #N/A? 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 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.
Basically, Excel reads text prefixed with an apostrophe as 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.
- This formula works the same in all versions of Microsoft Excel.
VLOOKUP is a fairly basic formula, but its applications are vast.
Download the Workbook
Enter your email address below to download the sample workbook.
Excel VLOOKUP Formula Video Tutorial
|Click the Full Screen button on the player to watch it in HD.|
More Excel VLOOKUP Formula Examples
Check out this list of our best VLookup tutorials