# Everyting about VLOOKUP

This amazing article is written by my friend Dave Bruns from (exceljet.net)

And yet, although VLOOKUP is a relatively easy to use, there is plenty that can go wrong. One reason is that VLOOKUP has a major design flaw â€” by default, it assumes you’re OK with an approximate match. Which you probably aren’t.

This can cause results that *look completely normal*, even though they are *totally incorrect*. Trust me, this is NOT something you want to try to explain to your boss, after she’s already sent your spreadsheet to management 🙂

Read below learn how to manage this challenge, and discover other tips for mastering the Excel VLOOKUP function.

### 1. How VLOOKUP works

VLOOKUP is a function to lookup up and retrieve data in a table. The “V” in VLOOKUP stands for vertical, which means the data in the table must be arranged vertically, with data in rows. (For horizontally structured data, see HLOOKUP).

If you have a well structured table, with information arranged vertically, and a column on the left which you can use to match a row, you can probably use VLOOKUP.

VLOOKUP requires that the table be structured so that lookup values appear in the left-most column. The data you want to retrieve (result values) can appear in any column to the right. When you use VLOOKUP, imagine that every column in the table is numbered, starting from the left. To get a value from a particular column, simply supply the appropriate number as the “column index”. In the example below, we want to look up the email address, so we are using the number 4 for column index:

In the above table, the employee IDs are in column 1 on the left and the email addresses are in column 4 to the right.

To use VLOOKUP, you supply 4 pieces of information, or “arguments”:

- The value you are looking for (
**lookup_value**) - The range of cells that make up the table (
**table_array**) - The number of the column from which to retrieve a result (
**column_index**) - The match mode (
**range_lookup**, TRUE = approximate, FALSE = exact)

Video: How to use VLOOKUP

### 2. VLOOKUP only looks right

Perhaps the biggest limitation of VLOOKUP is that it can only look to the right to retrieve data.

This means that VLOOKUP can only get data from columns to the right of first column in the table. When lookup values appear in the first (leftmost) column, this limitation doesn’t mean much, since all other columns are already to the right. However, if the lookup column appears inside the table somewhere, you’ll only be able to lookup values from columns to the right of that column. You’ll also have to supply a smaller table to VLOOKUP that starts with the lookup column.

You can overcome this limitation by using INDEX and MATCH instead of VLOOKUP.

### 3. VLOOKUP always finds the first match

If the lookup column contains duplicate values, VLOOKUP will match the first value only. If the first column in the table contains no duplicates, this obviously doesn’t matter. However, if the first column does contain duplicate values, VLOOKUP will only match the first instance. In this example, we are using VLOOKUP to find a first name. Although there are two “Janet”s in the list, VLOOKUP matches only the first:

### 4. VLOOKUP is not case-sensitive

When looking up a value, VLOOKUP does not process upper and lower case text differently. To VLOOKUP, a product code like “PQRF” is identical to “pqrf”. In the example below, we are looking for uppercase “JANET” but VLOOKUP does not distinguish case so it simply matches “Janet”, since that’s the first match it finds:

### 5. VLOOKUP has two matching modes

VLOOKUP has two modes of operation: exact match and approximate match. In most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you want to lookup information based on a unique key of some kind, for example, product information based on a product code, or movie data based on a movie title:

The formula in H6 to lookup year based on an exact match of movie title is:

1 |
<div><br /><pre><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>H4<span>,</span>B5:E9<span>,</span><span>2</span><span>,</span><span>FALSE</span><span>)</span> <span>//</span> <span>FALSE</span> <span>=</span> exact match |

However, you’ll want to use approximate mode in cases where you’re not matching on a unique id, but rather you’re looking up the “best match” or the “best category”. For example, perhaps you’re looking up postage based on weight, looking up tax rate based on income, or looking up a commission rate based on a monthly sales number. In these cases, you likely won’t find the exact lookup value in the table. Instead, you want VLOOKUP to get you the best match for a given lookup value.

The formula in D5 does an approximate match to retrieve the correct commission:

1 |
<div><br /><pre><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>C5<span>,</span>$G$5:$H$10<span>,</span><span>2</span><span>,</span><span>TRUE</span><span>)</span> <span>//</span> <span>TRUE</span> <span>=</span> approximate match |

### 6. Caution: VLOOKUP uses approximate match by default

Exact and approximate matching in VLOOKUP is controlled by the 4th argument, called “range lookup”. This name is not intuitive, so you’ll just have to memorize how it works.

For exact match, use FALSE or 0. For approximate match, set range_lookup to TRUE or 1:

1 |
<div><br /><pre><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>value<span>,</span>table<span>,</span>column<span>,</span><span>TRUE</span><span>)</span> <span>//</span> approximate match<br /><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>value<span>,</span>table<span>,</span>column<span>,</span><span>FALSE</span><span>)</span> <span>//</span> exact match |

Unfortunately, the 4th argument, range_lookup, is optional and defaults to TRUE, which means VLOOKUP will do an approximate match by default. When doing an approximate match, VLOOKUP assumes the table is sorted and performs a binary search. During a binary search, if VLOOKUP finds an exact match value, it returns a value from that row. If however, VLOOKUP encounters a value greater than the lookup value, it will return a value from the previous row.

This is a dangerous default because many people unwittingly leave VLOOKUP in it’s default mode, which can cause an incorrect result when the table is not sorted.

To avoid this problem, make sure to use FALSE or zero as the 4th argument when you want an exact match.

### 7. You can force VLOOKUP to do an exact match

To force VLOOKUP to find an exact match, make sure to set the 4 argument (range_lookup) to FALSE or zero. These two formulas are equivalent:

1 |
<div><br /><pre><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>value<span>,</span> data<span>,</span> column<span>,</span> <span>FALSE</span><span>)</span><br /><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>value<span>,</span> data<span>,</span> column<span>,</span> <span>0</span><span>)</span> |

In exact match mode, when VLOOKUP can’t find a value, it will return #N/A. This a clear indication that the value isn’t found in the table.

### 8. You can tell VLOOKUP to do an approximate match

To use VLOOKUP in approximate match mode, either omit the 4th argument (range_lookup) or supply it as TRUE or 1. These 3 formulas are equivalent:

1 |
<div><br /><pre><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>value<span>,</span> data<span>,</span> column<span>)</span><br /><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>value<span>,</span> data<span>,</span> column<span>,</span> <span>1</span><span>)</span><br /><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>value<span>,</span> data<span>,</span> column<span>,</span> <span>TRUE</span><span>)</span> |

We recommend that you always set range_lookup argument explicitly, even though VLOOKUP doesn’t require it. That way, you always have a visual reminder of the match mode you expect.

Video: How to use VLOOKUP for approximate matches

### 9. For approximate matches, data must be sorted

If you are using approximate mode matching, your data *must be sorted* ascending order by lookup value. Otherwise, you may get an incorrect results. Also be aware that sometimes text data may *look* sorted, even though it’s not.*Felienne Hermans has a great example of this problem here, from a cool analysis she did on actual Enron spreadsheets!*

### 10. VLOOKUP can merge data in different tables

A common use case for VLOOKUP is to join data from two or more tables. For example, perhaps you have order data in one table, and customer data in another and you want to bring some customer data into the order table for analysis:

Because the customer id exists in both tables, you can use this value to pull in the data you want with VLOOKUP. Just configure VLOOKUP to use the id value in table one, and the data in table 2, with the required column index. In the example below, we are using two VLOOKUP formulas. One to pull in the customer name, and the other to pull in the customer state.

### 11. VLOOKUP can classify or categorize data

If you ever need to apply arbitrary categories to data records, you can easily do so with VLOOKUP, by using a table that acts as the “key” to assign categories.

A classic example is grades, where you need to assign a grade based on a score:

In this case, VLOOKUP is configured for approximate match, so it’s important that the table be sorted in ascending order.

But you can also use VLOOKUP to assign arbitrary categories. In the example below, we are using VLOOKUP to to calculate a group for each department using a small table (named “key”) that defines the grouping.

### 12. Absolute references make VLOOKUP more portable

In situations where you plan to retrieve information from more than one column in a table, or if you need to copy and paste VLOOKUP, you can save time and aggravation by using absolute references for the lookup value and table array. This lets you copy the formula, and then change only the column index number to use the same lookup to get a value from a different column.

For example, because the lookup value and table array are absolute, we can copy the formula across the columns, then come back and change the column index as needed.

### 13. Named ranges make VLOOKUP easier to read (and more portable)

Absolute ranges are pretty ugly looking, so can make your VLOOKUP formulas a lot cleaner and easier to read by replacing absolute references with named ranges, which are automatically absolute.

For example, in the employee data example above, you can name the input cell “id” and then name the data in the table “data”, you can write your formula as follows:

Not only is this formula easier to read, but it’s also more portable, since named ranges are automatically absolute.

### 14. Inserting a column may break existing VLOOKUP formulas

If you have existing VLOOKUP formulas in a worksheet, formulas may break if you insert a column in the table. This is because hard-coded column index values don’t change automatically when columns are inserted or deleted.

In this example, the lookups for Rank and Sales were broken when a new column was inserted between Year and Rank. Year continues to work because it is on the left of the inserted column:

To avoid this problem, you can calculate a column index as described in the next two tips.

### 15. You can use ROW or COLUMN to calculate a column index

If you’re the type who is bothered by any amount of editing after copying a formula, you can use either ROW or COLUMN to generate dynamic column indexes. If you’re getting data from consecutive columns, this trick lets you set up one VLOOKUP formula, then copy it across with no changes required.

For example, with the employee data below, we can use the COLUMN function to generate a dynamic column index. For the first formula in cell C3, COLUMN by itself will return 3 (because column C is third in the worksheet) so we simply need to subtract one, and copy the formula across:

All formulas are identical with no post-editing required.

The formula we are using is this:

1 |
<div><br /><pre><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>id<span>,</span>data<span>,</span><a href="https://exceljet.net/excel-functions/excel-COLUMN-function"><span>COLUMN</span></a><span>()</span><span>-</span><span>1</span><span>,</span><span>0</span><span>)</span> |

### 16. Use VLOOKUP + MATCH for a fully dynamic column index

Taking the above tip one step further, you can use MATCH to look up the position of a column in a table and return a fully dynamic column index.

This is sometimes called a two-way lookup since you are looking up both the row and the column.

An example would be looking up sales for a salesperson in a particular month, or looking up the price for a particular product from a particular supplier.

For example, suppose you have sales per month, broken out by salesperson:

VLOOKUP can easily find the sales person, but it has no way to handle the month name automatically. The trick is to to use the MATCH function in place of a static column index.

Notice that we give match a range that includes all columns in the table in order to “sync up” the the column numbers used by VLOOKUP.

1 |
<div><br /><pre><span>=</span><a href="https://exceljet.net/excel-functions/excel-VLOOKUP-function"><span>VLOOKUP</span></a><span>(</span>H2<span>,</span>data<span>,</span><a href="https://exceljet.net/excel-functions/excel-MATCH-function"><span>MATCH</span></a><span>(</span>H3<span>,</span>months<span>,</span><span>0</span><span>),</span><span>0</span><span>)</span> |

Awesome article!