Watch the video extract as seen on YouTube, then read the full tutorial below.
|Click the Full Screen button on the player to watch it in HD.|
Excel VLOOKUP Exact Match Formulas Explained
VLOOKUP is my favourite Excel Formula! Perhaps because it was one of the first formulas I mastered that gave me an insight into the power of Excel and how it could help me in my job.
It enables you to get analysis and calculations done in minutes that would take hours manually. And once you master it you’ll find you have the occasion to use it all the time.
Interestingly there’s two ways you can use it 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 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.
In the list below we want to calculate a commission in column F for each builder. But 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.
The Syntax is:
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)
Firstly let me clarify some points:
1) ‘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.
2) ‘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, in Excel 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 and it allows 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?
Assuming the end result of our example exercise is to actually calculate the commission $ amount, we could make this formula even better by doing this 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!
1) 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, in our example 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’.
2) You can have as many columns as you like in your Table, just so long as you follow the ‘left to right’ rule above.
3) 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.
4) 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, Microsoft Excel would return the rate on the first instance of Doug.
5) The formula isn’t case sensitive, so ‘Doug’ could be ‘doug’ or ‘Doug’ in either column B or the table.
6) 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 value formatted as Text and another formatted as General. To check this go to each cell you’re referencing and look in the formula bar to see if one is prefixed by an apostrophe ‘. 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.
7) This formula works the same in Microsoft Excel 2003, 2007, 2010 and 2013.
The VLOOKUP is a fairly basic formula, but its applications are vast, especially when you combine it with other formulas like IF statements, SUMIF and so on.
Download the Excel workbook used in this example so you can practice to make sure you really get it and can take advantage of its power. Note: this is a .xlsx workbook, please ensure your browser doesn’t change the file extension on download.
More VLOOKUP Examples
Check out this list of our best VLookup tutorials
If you found this useful please share it with your friends and colleagues using the Google+1, LinkedIn, Facebook and Twitter buttons.