VLOOKUP is one of the most commonly used functions amongst experienced Excel users.
However, there are a number of traps that are easy to fall into but can be avoided with a few simple precautions.
This post explains how to set up a VLOOKUP from scratch, the pitfalls to watch out for and what to do if things go wrong.
Here is what is covered ...
1. What is VLOOKUP?
VLOOKUP is actually just one of a number of lookup functions.
The ‘V’ stands for Vertical.
VLOOKUP is used where you have a conventional table with column headings across the top and data arranged in rows.
There’s also HLOOKUP where, you guessed it, ‘H’ stands for Horizontal. HLOOKUP is used on transverse tables – that’s a table that is arranged with the headings listed down the left instead of across the top, and data is arranged left-to-right rather than top-to-bottom.
And then there’s LOOKUP, INDEX and MATCH.
But let's not get distracted. The focus of this blog post is the VLOOKUP function.
2. The Purpose & Structure of a VLOOKUP function
First you provide something that you want to find a match for in the first column of the table. This might be a name, an employee ID, a product code, a vehicle registration number … think about the data and table you work with, and you’ll know what you need.
The VLOOKUP will check in the first column of the table to try to find a match.
That’s the 10,000 foot view. Now, let’s dive a little deeper.
3. VLOOKUP: Deconstructed
The syntax of the VLOOKUP function is
=VLOOKUP(LookupValue, LookupRange, ColumnNo, RangeLookup)
The first 3 parameters are mandatory; the last is optional.
Consider this table:
Figure 01: The Employee table
Each employee has a unique Employee ID.
To make the formula more versatile, it’s a good idea to name your table. You don’t need to include the headings as you’ll never need to match on ‘Name’, but rather one of the names in the list.
So let’s name the data section of this employee table (cell range A2:G10) Emp_Table.
Quick tip: To name a cell or cell range:
1. Go to the name box and click to place the cursor in the middle (don’t click the drop-down arrow)
2. Type any name you like. Spaces are not allowed but underscores ( _ ) are.
3. Press Enter to register the name.
4. Examples of VLOOKUP in action
#1 Which department does employee ABC123 work in?
The formula is
=VLOOKUP(“ABC123”, Emp_Table, 5, FALSE)
Here’s the breakdown:
Figure 02: The parts of the table that the VLOOKUP formula refers to
Therefore, employee ABC123 works in Sales.
#2 What is the email address for employee “BCD234”?
=VLOOKUP("BCD234", Emp_Table, 7, FALSE)
#3 What is the extension number for employee “DEF456”?
=VLOOKUP("DEF456", Emp_Table, 4, FALSE)
#4 What is the full name of the employee whose ID is in cell A1?
=CONCATENATE(VLOOKUP(A1, Emp_Table, 2, FALSE), " ", VLOOKUP(A1, Emp_Table, 3, FALSE))
This formula uses two VLOOKUP formulas to return the first name and last name, then concatenates (joins) them together.
This makes use of the CONCATENATE function which joins together all the elements inside its brackets. You can read more about that in this blog post (link).
5. Which of these VLOOKUP mistakes are you making?
Mistake 1: Is your key field unique?
The first column in the table must contain unique entries. There cannot be any duplicates.
If there are duplicates, then the VLOOKUP function may sometimes get it right, but may sometimes get it wrong, which makes it as useful as a chocolate teapot.
In typical tables where the first field contains employee ID, supplier ID, product code, job number etc. these are unique by design, so barring human error, you should be okay.
However if you are performing a lookup on a field such as First Name or Product Name it’s highly likely that a duplicate entry could be created. For example, you might end up with 2 Joes or 15 Bruces.
Even if you try to get around the problem by concatenating (joining) the first name with the last name, it’s still possible to end up with 2 Joe Dodgys or 3 Bruce Deuces.
Mistake 2: Is your column index number numeric?
The 3rd parameter of the VLOOKUP function asks for the column NUMBER that contains the data you want. Many people make the mistake of using the Excel column letter (like ‘E’ instead of 5) which results in a formula error.
Mistake 3: Have you specified the right column number?
The table could be positioned anywhere on the worksheet. It doesn’t always start in column A - it might be in columns T to Z.
Let’s say that the data you need is in the 3rd column of the table, that starts in column T. The Column Number is still ‘3’
Mistake 4: Hidden spaces
Hidden spaces at the beginning or end of your lookup value or your lookup range can be hard to spot because you can’t see them, but they will determine whether or not a match is found.
Often when you import data from systems like SAP, data has extra leading or trailing spaces. You can quickly remove these with the TRIM function.
=TRIM(A1)
Mistake 5: Have you specified a match type?
For any text-based matches, the match type is always EXACT, so you must specify FALSE for the match type. Excel will either find a match or it won’t.
If you do not specify a match type, Excel uses the default setting of TRUE which tries to find a closest match. For text-based matches a TRUE match type will give an error or return the wrong result. Either way, it’s not 100% reliable.
So, you may be asking …
6. VLOOKUP using CLOSEST MATCH
Let’s imagine you want to find the postage cost for a package. Here’s an example spreadsheet.
Figure 03: Pricing table
Weights are banded (1–100g, 101–200g etc.) and the table shows the price for each weight band.
The main problem manifests itself quickly when an input weight is entered that does not match any of the lower weight entries, and therefore when an EXACT match is used, a #N/A! error is returned. This will be a frequent occurrence with this type of data.
Figure 04: What happens when there is no EXACT match
By switching to a CLOSEST MATCH type (by changing the 4th parameter to TRUE), Excel will match the next lowest value in the table.
Let’s say the input weight is 225 and you use a closest match.
=VLOOKUP(225, Weight_Price_Table, 3, TRUE)
The closest value below 225 is 201.
The VLOOKUP function then looks along that row and selects the data in the third column ($300).
Figure 05: A competed formula using CLOSEST match
Even if the input weight was 300, the price would still be $3.00.
But when the weight clicks over to 301, the price is $3.25.
7. Watch the video (over the shoulder demo)
8. What next?
If you have never written a VLOOKUP formula before I hope this has given you the start you need.
If you have struggled with VLOOKUPs in the past I hope this post has removed the mystery and given you some handy pointers.
I'd love to hear your experience with VLOOKUP formulas. How do you use them?
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
