# Vlookup in Excel

July 31, 2012

Vlookup (pronounced ”*vee-look-up*”) is one of the most popular look up functions
in Excel. When you need to extract data from a table based on a particular
value, you can use Vlookup to do that. In simple terms, it looks at a table in
the first column for a specific value and returns a cell in the same row where
you choose the column to return from. You get to choose whether you want to
find an approximate match or an exact match (more on which one to choose later).
You can use this for both text searches as well as numeric searches, which
proves to be a powerful function and can easily help you out in your everyday
tasks at work.

What we’ll be talking about:

- Vlookup function basics
- Vlookup with text searches
- Vlookup with numeric searches
- When to choose approximate match versus exact match

Let’s get started.

## Vlookup Function Basics

The Vlookup function has the form of:

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup = TRUE])`

Here is a simple explanation of each parameter (for those of you who don’t know, a parameter is, check out this post):

Parameter | Notes |
---|---|

`lookup_value` |
The number or text sought for. |

`table_array` |
A cell reference of the table range. Note: Do NOT include headers in your cell reference if you are searching for text and an approximate match. Trust me, you won’t get the desired results in some cases (more on that later). |

`col_index_num` |
The number of the column you want to return. This number is based on the table you pass, not by the column character’s number equivalent. For example, if your range is `B2:D5` , column 1 is `B` , column 2 is `C` , and column 3 is `D` . |

`range_lookup` |
Can either be `TRUE` or `FALSE` . It s an optional parameter where if left out, the value is set to `TRUE` . If `TRUE` , the function will look for an approximate match. This means that for a numerical search, it will return the closest number smaller than the value you’re looking for, if it does not find an exact match. If `FALSE` , the function will look only for an exact match. And if no exact match is found, an `#NA!` error will be returned. I should note that if you choose `TRUE` for `range_lookup` , the first column in the table must be sorted from smallest to largest (or for text, alphabetical). |

## Vlookup when searching for text

Here I create a simple table to illustrate using Vlookup when searching through text. Let’s say that I want to find the age of someone based on the name we’re searching for.

(If you’re curious as to how I have those little dropdown buttons next to each header name, select your table - including the headers - and click Sort & Filter -> Filter)

Say we want to find Ray’s age using the Vlookup formula. Here’s how we would do it:

`=VLOOKUP("Ray",$A$2:$B$6,2,FALSE) Result: 31`

Here is the formula breakdown (each parameter is separated by a comma):

Parameter | Notes |
---|---|

`"Ray"` |
The text we’re searching for in the first column of the table array we will pass. (Hint: this can be a cell reference instead of just plain text) |

`$A$2:$B$6` |
The table array. We have to include column `B` because that is what we want to return based on the text we find in `A` . |

`2` |
This is the column number. |

`FALSE` |
This tells the function that we want an exact match. A nice thing about using `FALSE` here is that we don’t have to worry about the table being sorted. I’ll get into what happens when we use `TRUE` in a little bit. |

When the formula is evaluated by Excel, it looks in each row of column `A`

for an
exact match on “Ray” which we passed as the parameter to search for. The
formula then finds “Ray” in the second row of the table we passed (remember, the
first row/column starts and the top-left-most cell in the table passed to the
function). When it finds “Ray” it moves to column 2 in the table of the same
row and returns 31, which is correct.

## Vlookup when searching for numbers

It’s more likely that you’ll search for an approximate match when looking for a
number when using Vlookup (or at least that’s what **I** think, anyway). Check
out this basic table showing temperature and corresponding “Real Feel.”

Let’s look at some of the return values by playing around with the Vlookup formula. I’ll do a Vlookup on a temperature of 0.03 using this formula:

`=VLOOKUP(0.03,$A$2:$B$8,2,TRUE) Result: "Ridiculously Cold"`

Let’s look at the formula breakdown and then we’ll step through how it came to that result:

Parameter | Notes |
---|---|

`0.03` |
The number we’re trying to find a close match for. |

`$A$2:$B$8` |
The table array (again, including column `B` so we can return the “Real Feel”). |

`2` |
The column number we want to return. In this case, the “Real Feel” value. |

`TRUE` |
This time we tell the function that we want an approximate match. So when we looked for `0.03` , we found a match for `0.01` in the table. Again, remember that when we set this value to `TRUE` we must have the table sorted and we cannot have the header as part of the table range (I’ll explain why in a little bit). |

The way Excel steps through this formula is similar to the last one we looked at:

- It starts by looking at the table’s first column for a value of 0.03 or something close (approximate).
- Starts with 0.01. Since 0.01 is less than 0.03, it moves to the next one.
- Then it looks at 32. Since 32 is greater than 0.03, it reverts back to 0.01 as the closest match.
- Now it goes to column 2 and return the corresponding value next to 0.01’s value, which is “Ridiculously Cold.”

If we were to change `TRUE`

to `FALSE`

, we would receive the dreaded `#NA!`

error since there is no `0.03`

value in the table. The `range_lookup`

value can
be more important than people realize when using `Vlookup`

.

So what if we tried searching for 31 instead of 0.03? Should we have “Freezing Cold” be returned? Let’s take a look.

`=VLOOKUP(31,$A$2:$B$8,2,TRUE) Result: "Ridiculously Cold"`

What?! It returned “Ridiculously Cold”? But 31 is WAY closer to 32 than it is to 0.01. What gives?

Remember what I said before about the `range_lookup`

parameter for numerical
searches?

For a numerical search, it will return the closest number

smallerthan the value you’re looking for,ifit does not find an exact match.

Let’s step through how Excel evaluated this formula:

- Is 0.01 less than or equal to 31? Yes, it is less. So keep moving down.
- Is 32 less than or equal to 31? No, so we can stop looking.
- 0.01 is considered the closest approximate match in this scenario, as odd as it may seem.

It’s clear that searching for an approximate value will sometimes seem less logical than we think it should behave. Let’s look at the quirks of Vlookup and see if we can make some sense out of it.

## Approximate match or exact? Which one should I choose?

The `range_lookup`

parameter is what controls whether we tell the Vlookup
function should look for an exact match or an approximate match. This setting
can be a bit tricky so it’s good to remember how the function will be behave
based on this value and which one you should choose when using the Vlookup
function.

### Exact Match

This is just my opinion, but I feel that searching for an exact match makes this
function much easier to use. This is especially true when searching for a text
value (which I feel is what most people actually use when people use text for
the `range_lookup`

parameter). Chances are you would only want to find an exact
match when searching for text.

A nice feature of searching for an exact match is that you don’t have to worry about sorting the table by the column you’re searching for. This way, when your table grows, the formula still works correctly.

Also, with exact searches, you can safely include the header row when you pass the table parameter.

### Approximate Match

Approximate matches are probably best-suited for numerical searches. Searching for exact matches might be more difficult when your numeric values contain fractions of numbers. I would imagine that in most cases, looking for an exact numeric match would be very difficult when dealing with fractional numbers. I feel that approximate match is more appropriate in this kind of situation.

When searching for text, however, it’s difficult to find a good reason to look for an approximate match. And what would be considered an approximate match, anyway? If we search for “Baub,” would “Bob” be an approximate value? In this case, no. That’s because alphabetically, “Baub” comes before “Bob.”

Yet, there are times where the approximate match doesn’t seem to make the most sense like we saw earlier. I guess it’s in the name “approximate.” When you hear that, you think of what’s closest to what we’re looking for. To help remember how approximate matching works, think of it as “the closest match less than or equal to the value sought for.”

## What’s with excluding the header row?

OK, I know I mentioned this a few times in this post. Here’s the thing, when you search for text using approximate match and you include the header row as part of your table, you will not always get the desired results. Let me show you an example. Let’s look at our first table:

This list is sorted by name. Check out this formula:

`=VLOOKUP("Bob",$A$1:$B$6,2,TRUE) Result: #NA!`

We have the `range_lookup`

value set to `TRUE`

which means to look for an
approximate match. Yet we get an error in return. This is really strange,
because if we change the formula to this:

`=VLOOKUP("Bob",$A:$B,2,TRUE) Result: 27`

It doesn’t complain. In my opinion, try to either use FALSE for `range_lookup`

or just don’t include your headers if you have to search for an approximate
match.

I hope you enjoyed this post and if you did, please subscribe! You will get notified of newer posts the moment they are published. Also, if you have any questions or comments, or if you want to see me post about something, please leave a comment below. It really helps me to make this blog better and I would love to hear from you.