VLOOKUP Function

VLOOKUP is the most popular function in Excel for basic lookups. This is because VLOOKUP is easy to use and does something useful: it scans a table, finds a match, and returns a result in the matching row. Because this kind of problem appears in almost any industry, VLOOKUP is used in all kinds of businesses.

This article describes how to use VLOOKUP in simple language. It takes a step-by-step approach, first explaining how VLOOKUP works, and then showing how VLOOKUP can be used to solve a variety of problems. Use the links below to jump directly to a topic of interest:

Vertical data | Column Numbers | Only looks right | Matching Modes | Exact Match | Approximate Match | Danger | First Match | Wildcard Match | Two-way Lookup | Multiple Criteria | #N/A Errors

V is for vertical

The purpose of VLOOKUP is to look up and retrieve information in a table organized vertically:

For example, with the table above, you can use VLOOKUP to find the amount for a given order like this:

With the order number 1005 as a lookup value in cell I4, the result is 125. VLOOKUP scans the first column of the table, matches order number 1005, and returns 125, the amount. The formula in cell I5 is:

=VLOOKUP(I4,B5:F9,3,FALSE)

For now, just pay attention to these things:

1. The lookup value is provided as I4. If this value is changed, VLOOKUP will return a new result.
2. The lookup table is provided as the range B5:F9, which is the entire table.
3. The lookup values (order numbers) are in the first column of the table.
4. The result values (order amounts) are in the third column of the table.
5. The column index number is given as 3.

VLOOKUP uses column numbers

The VLOOKUP function uses column numbers to indicate the column from which a value should be retrieved. When you use VLOOKUP, imagine that every column in the table is numbered, starting at 1:

To retrieve a value from a given column, just provide the number for column_index_num. For example, to retrieve the first name in cell H4, we use 2 for column_index_num, as seen above. By changing only the column number, we can retrieve the first name, last name, and email address by asking for columns 2, 3, and 4:

=VLOOKUP(H3,B4:E13,2,FALSE) // first name
=VLOOKUP(H3,B4:E13,3,FALSE) // last name
=VLOOKUP(H3,B4:E13,4,FALSE) // email address

Notice the only difference in the formulas above is the column number.

VLOOKUP only looks right

VLOOKUP can only look to the right. In other words, you can only retrieve data to the right of the first column in the table provided to VLOOKUP. For example, to look up information by ID in the table below, we must provide the range D3:F9 as the table, and that means we can only look up Email and Department:

This is a fundamental limitation of VLOOKUP — the first column of the table must contain lookup values, and VLOOKUP can only access columns to the right. To retrieve information to the left (or right) of a lookup column, you can use the XLOOKUP function or an INDEX and MATCH formula.

Two match modes

VLOOKUP has two match modes: exact match and approximate match. The match mode used by VLOOKUP is controlled by the last argument, range_lookup. The word "range" in this case refers to "range of values" – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. When range_lookup is FALSE, VLOOKUP will only allow an exact match:

=VLOOKUP(value,table,col_index,TRUE) // approximate match
=VLOOKUP(value,table,col_index,FALSE) // exact match

Exact match example

In most cases, you'll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:

The formula in H6 to find Year, based on an exact match of the movie title, is:

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

With "Toy Story" in H4, VLOOKUP finds a match in the fourth row in the table and returns 1995 as a result.

Approximate match example

In some cases, you will need an approximate match lookup instead of an exact match lookup. A good example is the problem of assigning a letter grade based on a score. In the worksheet below, we want to use the scores in column C to assign a grade using the table to the right in the range F5:G9, which is named "key". Here, we need to use VLOOKUP in approximate match mode, because in most cases the exact score in column C will not be found in column F. The VLOOKUP formula in D5 is configured to perform an approximate match by setting the last argument to TRUE:

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

As the formula is copied down, VLOOKUP will scan values in column F looking for the score in column C. If an exact match is found, VLOOKUP will return the corresponding grade in that row. If an exact match is not found, VLOOKUP will stop at the first larger score, then "step back" and return the grade from the previous row.

Video: How to use VLOOKUP for approximate match

The default is dangerous

It is important to understand that VLOOKUP will perform an approximate match by default. This happens because range_lookup is optional and defaults to TRUE. This can cause big problems if you expect an exact match, as seen in the worksheet below. Here, the goal is to look up the amount for a given invoice number in cell F5. The formula in G5 is:

=VLOOKUP(F5,B5:D10,3) // approximate by default!

No value has been provided for range_lookup, so VLOOKUP performs an approximate match. Notice that invoice number 100235 does not exist in the data, but VLOOKUP returns 12,000. This happens because VLOOKUP scans the table until it reaches invoice 100236, then it "steps back" to invoice 100234 and returns the (incorrect) amount in that row:

To fix this problem, enable exact matching by providing range_lookup as FALSE or 0:

=VLOOKUP(F5,B5:D10,3,FALSE) // exact match

This will cause VLOOKUP to return an #N/A error when an invoice number doesn't exist. If you like, you can use the IFNA function to return a more friendly result, as explained below.

First match only

If there is more than one matching value in a table, VLOOKUP will only find the first match. In the screen below, VLOOKUP is configured to find the price for the color "Green". There are three rows with the color Green, and VLOOKUP returns the price in the first row, which is $17.00. The formula in cell F5 is:

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

To retrieve the last match in a set of data, you can use the XLOOKUP function configured to perform a "reverse search". To retrieve all matches, use the FILTER function.

Wildcard match

The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. To use wildcards with VLOOKUP, you must provide FALSE or zero (0) for range_lookup. In the screen below, the formula in H7 retrieves the first name, "Michael", after typing "Aya" into cell H4. Notice the asterisk (*) wildcard is concatenated to the lookup value inside the VLOOKUP formula:

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

For a full explanation of this formula, see this example.

Two-way lookup

Inside the VLOOKUP function, column_index_num is normally hard-coded as a static number. However, you can create a dynamic column index by using the MATCH function to locate the desired column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns. In the screen below, VLOOKUP is configured to perform a lookup based on Name and Month like this:

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

MATCH locates "Feb" in the range B4:E4 and returns 3 to VLOOKUP as the column number. For more details, see this example.

Note: In general, INDEX and MATCH is a more flexible way to perform two-way lookups.

Multiple criteria

The VLOOKUP function does not handle multiple criteria natively. However, you can use a helper column to join multiple fields together and use these fields like multiple criteria inside VLOOKUP.  In the example below, Column B is a helper column that concatenates first and last names together with this formula:

=C5&D5 // helper column

VLOOKUP is configured to do the same thing to create a lookup value. The formula in H6 is:

=VLOOKUP(H4&H5,B5:E13,4,0)

For details, see this example. For a more advanced, flexible approach, see this example.

Note: INDEX and MATCH and XLOOKUP are better for lookups based on multiple criteria.

VLOOKUP and #N/A errors

If you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error simply means "not found". For example, in the screen below, the lookup value "Toy Story 2" does not exist in the lookup table, and all three VLOOKUP formulas return #N/A:

The #N/A error is useful because tells you something is wrong. There are several reasons why VLOOKUP might return an #N/A error, including:

• The lookup value does not exist in the table
• The lookup value is misspelled or contains extra space
• Match mode is exact, but should be approximate
• The table range is not entered correctly
• The formula was copied, and the table reference is not locked

To "trap" the NA error and return a custom value, you can use the IFNA function like this:

The formula in H6 is:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

The message can be customized as desired. To return nothing (i.e. to display a blank result) when VLOOKUP returns #N/A you can use an empty string ("") like this:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

You can also use the IFERROR function to trap VLOOKUP #N/A errors. However, use caution with IFERROR, because it will catch any error, not just the #N/A error. Read more: VLOOKUP without #N/A errors 

Performance

VLOOKUP in exact match mode can be slow on large data sets. On the other hand, VLOOKUP in approximate match mode is very fast, but dangerous if you need an exact match because it might return an incorrect value. If you have data sorted by lookup value, it is possible to create a fast exact match formula by using VLOOKUP twice.

Notes: (1) This approach is overkill unless performance is a problem. (2) newer functions like XLOOKUP have built-in support for a fast binary search.

More about VLOOKUP

• VLOOKUP with multiple criteria (basic)
• VLOOKUP with multiple criteria (advanced)
• How to use VLOOKUP to merge tables
• 23 tips for using VLOOKUP
• More VLOOKUP examples and videos
• XLOOKUP vs VLOOKUP

Other notes

• VLOOKUP performs an approximate match by default.
• VLOOKUP is not case-sensitive.
• Range_lookup controls matching. FALSE = exact, TRUE = approximate (default).
• If range_lookup is omitted or TRUE:
  • VLOOKUP will match the nearest value equal to or less than the lookup_value.
  • Column 1 of table_array must be sorted in ascending order.
• If range_lookup is FALSE or zero for an exact match:
  • VLOOKUP will perform an exact match.
  • The table_array does not need to be sorted.