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:

- 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
- Search
**FIRST column**to find the**ROW**containing the lookup_value - Search and return corresponding value in column col_index_num of the table_array

- Search
- 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 – search for the first EXACT MATCH in the first column of the table_array
- TRUE – Default option. assume the FIRST COLUMN in table_array is sorted either numerically or alphabetically and return an APPROXIMATE MATCH.
**This parameter works correctly ONLY IF THE FIRST COLUMN OF THE table_array IS SORTED!**

## 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*. Feel free to share your comments/thoughts below or on the AnalystCave forum.