The Excel VLOOKUP function (Vertical Lookup) is one of need-to-know features in Excel! I can’t stress enough how many articles/posts out there have been published on using VLOOKUP. Probably rightly so. I couldn’t however resist to make an my attempt at consolidating all the knowledge around how to use VLOOKUP and all the bits and pieces of information you need to know to be able to fully leverage the vertical lookup function in Excel! Let’s start with a VLOOKUP example…
The VLOOKUP Phonebook analogy
To quickly explain what VLOOKUP does I usually like to use the common Phonebook analogy. In the example below imagine we have a friend called John White. Let’s say we want to call John. What we need to do is to locate John in our Phonebook by his Name and Surname and call him on his corresponding phone number located within the same row.
Everyday we deal with similar lookup problems e.g. restaurant menu (looking up the dish price), grocery lists (how many bottles of milk did I need to buy?) etc. Hence the usefulness of using VLOOKUP. Let us move on to learn how to use VLOOKUP.
How to use VLOOKUP?
Considering the above VLOOKUP example let’s take a closer look at the parameters of the VLOOKUP function:
Let’s look at the arguments of VLOOKUP in more detail:
| Parameter | Description |
|---|---|
| lookup_value | value you want to look up. Must be in the first column of the range of cells you specify in table-array |
| table_array | range of cells VLOOKUP will
|
| col_index_num | column number within the table_array that contains return values. The first left-most column in the table_array starts numbering with 1 |
| range_lookup | an OPTIONAL boolean (True/False) parameter that defines how the VLOOKUP function will behave:
|
FALSE VLOOKUP Example
Nothing beats a hands-on VLOOKUP example! See the below animation to witness for yourself how VLOOKUP works!
Notice that I am using the range_lookup set to FALSE. This means that the VLOOKUP function will always look for an EXACT MATCH.
TRUE VLOOKUP Example
In the previous example the range_lookup parameter was set to FALSE. This example will equally work with range_lookup equal to TRUE as long as we sort our FIRST COLUMN alphabetically (or numerically in other cases)!
So what’s the difference you will ask? PERFORMANCE. The TRUE (approximated match) VLOOKUP is significantly faster than the FALSE (exact match) VLOOKUP. Read here more on VLOOKUP Performance.
Important! If you got hooked up already on the performance bit of the TRUE VLOOKUP beware! The TRUE VLOOKUP will always return a result… even if it didn’t find an exact match. That is a setback. Luckily Excel experts have found a way around this with a DOUBLE TRUE VLOOKUP. Read on.
DOUBLE TRUE VLOOKUP
To exemplify the DOUBLE TRUE VLOOKUP let’s consider this table:
We want to match the Animal Monkey against a Category. If we use a standard FALSE VLOOKUP like this:
=VLOOKUP("Monkey";A1:B5;2;FALSE)
We will get the following results:
"Mammal" if found "#N/A" if not found
Great! But what happens if we use a simple TRUE VLOOKUP?:
=VLOOKUP("Monkey";A1:B5;2;TRUE)
We will get the following results:
"Mammal" if found "Amphibian" (or similar) if not found
Well that’s not very appropriate huh? How can we know for sure if the TRUE VLOOKUP cross-referenced the Animal correctly? Well.. why not use a second VLOOKUP for that? Consider the DOUBLE VLOOKUP below:
=IF(VLOOKUP("Monkey";A1:B5;1;TRUE)="Monkey";VLOOKUP("Monkey";A1:B5;2;TRUE);)
We will get the following results:
"Mammal" if found "" if not found (replace "" with #N/A if needed)
See what happens here? The first VLOOKUP validates whether there is an EXACT MATCH in the first column. If so the second VLOOKUP will return the corresponding result. See below:
Are 2 DOUBLE TRUE VLOOKUPs really worth the trouble? If you are looking for performance than YES! Read more here.
INDEX MATCH vs VLOOKUP – Do’s and Don’t
If you’ve been using VLOOKUP long enough you probably stumbled across MANY articles/posts on why many consider a certain combination of two Excel functions i.e. INDEX MATCH, better than using a regular VLOOKUP. In fact MOST Google results for VLOOKUP vs INDEX MATCH won’t mention the significant advantage VLOOKUP has over INDEX MATCH which I hinted in the previous section.
Let’s compare the pro’s and con’s of using INDEX MATCH (instead of VLOOKUP):
- More flexible – allows you to match both against rows and columns
- Less error prone – adding/removing columns/rows from the lookup table should not crash the INDEX-MATCH combo
- Both vertical and horizontal lookups – VLOOKUP and HLOOKUP address either only vertial or horizontal lookups, whereas with the INDEX MATCH you can easily do both
- Harder to use – VLOOKUP is a little easier to understand than INDEX-MATCH and I know some people have difficulty with this two step approach
- Slower (at least in newer versions of Excel like 2013) – opposed to the broadly shared myth INDEX MATCH is no longer faster than a simple VLOOKUP. Although, I still think INDEX-MATCH is better in most cases. Read my post here for how VLOOKUP compares in terms of performance against INDEX MATCH.
VLOOKUP with Multiple criteria
In some cases you would like to run a VLOOKUP against MORE THAN ONE COLUMN i.e. lookup a certain set of values against several columns instead of just one column. There is a simple way to achieve this by introducing a HELPER COLUMN with a concatenation of the lookup columns. Let’s consider the example below:
As you can see each month is specified in two separate columns. A simple one-column VLOOKUP will not do. So how to use VLOOKUP to get the result for multiple criteria? Using a HELPER COLUMN! See the solution below:
The above is the simplest approach to a multiple criteria VLOOKUP. There are more elegant approaches that need not a Helper Column e.g. check-out Chandoo’s example here.
VLOOKUP Performance
If you are impatient for the answer – for best performance always use DOUBLE TRUE VLOOKUPS. Now for a more thorough explanation let’s start from the beginning and summarize what we know in bullet points:
- The VLOOKUP function can be well replaced with other functions/features in Excel
- A common practice it to replace a VLOOKUP with a INDEX MATCH function combo. This does not affect performance (much) but solve a lot of typical usability issues
- I have hinted above that a TRUE (approximated) VLOOKUP on a sorted table_array will have better performance that a regular FALSE (exact match) VLOOKUP
The TRUE (approximate) VLOOKUP function seems to be best candidate in terms of performance. However, approximating the result may render certain issues – a TRUE VLOOKUP will ALWAYS return a result! Even if there is no exact match! This creates a certain issue for us. Luckily there is a certain trick to use 2 TRUE VLOOKUPs in a combo to replace a regular FALSE VLOOKUP and expect similar results. This is commonly called a DOUBLE TRUE VLOOKUP.
Performance: VLOOKUPs VS INDEX MATCH VS SQL
The below performance comparison was carried out by me in my separate post on VLOOKUP vs INDEX-MATCH vs SQL. Follow to my post to read more. In the meantime let’s look at the comparison:
What do the various categories mean?:
- VLOOKUP (sorted) – a regular FALSE (exact match) VLOOKUP against a sorted table_array
- DOUBLE TRUE VLOOKUP (sorted) – 2 combined TRUE (approximate match) VLOOKUPs against a sorted table_array
- INDEX-MATCH (sorted) – a combination of the INDEX and MATCH functions against a sorted table_array
- SQL (Sorted) – an Excel MS Query (SQL) executed against a sorted table_array
How to use VLOOKUP recap
Well I do hope this exhausts the subject of using VLOOKUP in Excel. VLOOKUP is a common used function in Excel sometimes wrongly which, I hope from my performance stats above, can seriously tamper with your Workbook performance causing Excel Workbooks to recalculate in matters of minutes instead of seconds.
