# Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the *first* column of the table passed into VLOOKUP.

**value**- The value to look for in the first column of a table.**table**- The table from which to retrieve a value.**col_index**- The column in the table from which to retrieve a value.**range_lookup**- [optional] TRUE = approximate match (default). FALSE = exact match.

VLOOKUP is an Excel function to get data from a table organized vertically. Lookup values must appear in the *first* column of the table passed into VLOOKUP. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches.

### V for vertical

The purpose of VLOOKUP is to get information from a table organized like this:

Using the Order number in column B as a lookup value, VLOOKUP can get the Customer ID, Amount, Name, and State for any order. For example, to get the customer name for order 1004, the formula is:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

For horizontal data, you can use the HLOOKUP, INDEX and MATCH, or XLOOKUP.

### VLOOKUP retrieves data based on column number

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, provide the appropriate number as the "column index". For example, the column index to retrieve first name below is 2:

The last name and email can be retrieved with columns 3 and 4:

### VLOOKUP only looks right

VLOOKUP can only look to the right. The data you want to retrieve (result values) can appear in any column *to the right* of the lookup values:

If you need to lookup values to the left, see INDEX and MATCH, or XLOOKUP.

### Exact and approximate matching

VLOOKUP has two modes of matching, exact and approximate. The name of the argument that controls matching is "**range_lookup**". This is a confusing name, because it seems to have something to do with *cell ranges* like A1:A10. Actually, 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. A good example of this is using VLOOKUP to calculate grades.

It is important to understand that **range_lookup** *defaults to TRUE*, which means VLOOKUP will use approximate matching by default, which can be dangerous. Set **range_lookup** to FALSE to force exact matching:

*Note: You can also supply zero (0) instead of FALSE for an exact match.*

### Example 1 - Exact match

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 movie title, is:

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

### Example 2 - Approximate match

In cases when you want the *best match*, not necessarily an *exact match*, you'll want to use approximate mode. For example, below we want to look up a commission rate in the table G5:H10. The lookup values come from column C. In this example, we need to use VLOOKUP in *approximate match* mode, because in most cases an exact match will never be found. 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

VLOOKUP will scan values in column G for the lookup value. If an exact match is found, VLOOKUP will use it. If not, VLOOKUP will "step back" and match the previous row.

*Note: data must be sorted in ascending order by lookup value when you use approximate match mode with VLOOKUP.*

### Example 3 - VLOOKUP and #N/A errors

If you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error just 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:

One way to "trap" the NA error is to use the IFNA function like this:

The formula in H6 is:

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:

The #N/A error is useful because it tells you something is wrong. In practice, there are many reasons why you might see this 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
- You are copying VLOOKUP, and the table reference is not locked

Read more: VLOOKUP without #N/A errors

### More about VLOOKUP

### Other notes

**Range_lookup**controls whether**value**needs to match exactly or not. The default is TRUE = allow non-exact match.- Set
**range_lookup**to FALSE to*require*an exact match and TRUE to*allow a non-exact match*. - If
**range_lookup**is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is*still less than*.**value** - When
**range_lookup**is omitted, the VLOOKUP function will allow a non-exact match, but it__will use__an exact match if one exists. - If
**range_lookup**is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value. - If
**range_lookup**is FALSE (require exact match), values in the first column of**table**do not need to be sorted.