Excel VLOOKUP formulas explained

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.

To get the full video with troubleshooting tips for VLOOKUP formulas sign up for our free Microsoft Office Online Training.

Excel VLOOKUP Exact Match Formulas Explained

VLOOKUP is my favourite Excel Formula!  It helps me get analysis and calculations done in minutes that would take hours manually.  And I find I 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 VLOOKUP Exact Match version of the formula, but first let’s set the scene. The other way to do a VLOOKUP 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.

Microsoft Excel describes the VLOOKUP formula as:

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 VLOOKUP formula in column F for the above example would be:

=VLOOKUP(B2,$H$2:$I$9,2,FALSE)   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.  To understand how to use shortcuts like absolute references you can get our Microsoft Office online training here.

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:

=VLOOKUP(B2,$H$2:$I$9,2,FALSE)*E2

And in seconds we can have hundreds of VLOOKUP’s and calculations done!

VLOOKUP 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 doesn 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’, but that lesson is for another day.

7) This formula works the same in Microsoft Excel 2003, 2007 and 2010.

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 copy and practice the VLOOKUP formula to make sure you really get it and can take advantage of its power.

Update: The second way to use a VLOOKUP formula is what I call the Sorted List method.  Click here to go to my tutorial on the VLOOKUP Sorted List Method.

Tell us how you use VLOOKUP formulas in your work by entering a comment below.

Did you find this useful?  Why not share the love and let your friends know.  Click the icons below for Twitter and Facebook.

Want more? Sign up for our newsletter below and receive weekly tips & tricks to your inbox, plus you’ll get our 100 Excel Tips & Tricks e-book.

Tagged as: vlookup