# Excel VLOOKUP Function

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.

**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 designed to retrieve data in a table organized into vertical rows, where each row represents a new record. The "V" in VLOOKUP stands for vertical:

If you have data organized horizontally, use the HLOOKUP function.

#### VLOOKUP only looks right

VLOOKUP requires a lookup table with lookup values in the left-most column. The data you want to retrieve (result values) can appear in any column to the right:

#### 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, simply supply the appropriate number as the "column index":

#### VLOOKUP has two matching modes, exact and approximate

VLOOKUP has two modes of matching: exact and approximate, which are controlled by the 4th argument, called "range_lookup". Set range_lookup to FALSE to force exact matching, and TRUE for approximate matching.

*Important: range_lookup defaults to TRUE, so VLOOKUP will use approximate matching by default:*

### 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 lookup year based on an exact match of movie title is:

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

### Example 2: Approximate match

You'll want to use approximate mode in cases when you're looking for the best match, not an exact match. A classic example is finding the right commission rate based on a monthly sales number. In this case, you want VLOOKUP to get you the best match for a given lookup value. In the example below, the formula in D5 performs an approximate match to retrieve the correct commission.

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

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

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.

## Excel Formula Training

Learn Excel formulas and functions with concise, clear videos. Master absolute/relative addresses, dates, text, named ranges, and tools for troubleshooting. Each video comes with a practice worksheet and audio transcript. Instant access and complete 100% guarantee. Start today!