How to Use VLOOKUP in Excel

VLOOKUP searches for a value in the first column of a table and returns a value from a column to the right of it. In financial models it joins data across tabs, pulling a tax rate by jurisdiction, a price by SKU, or an account name by code. It is the most taught lookup in Excel, but its design has fragile spots that matter once a model grows past a few rows.

Syntax and how it works

The signature is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). VLOOKUP scans the first column of table_array top to bottom for lookup_value, then returns the cell in the same row that sits col_index_num columns to the right.

The fourth argument controls match type and is the one analysts get wrong. FALSE or 0 demands an exact match and is what you almost always want. TRUE or 1, which is also the default if you omit the argument, performs an approximate match and assumes the first column is sorted ascending. For tiered lookups like commission bands an approximate match is useful, but for joining on an ID an unsorted table with TRUE returns silent wrong answers.

• lookup_value must live in the first column of the table, so VLOOKUP cannot look to the left.
• col_index_num is counted from the table's first column, not from column A of the sheet.
• Always pass 0 for exact joins on codes, IDs, and names.

Worked example: price by product code

A reference tab holds product codes in B2:B6 and prices in D2:D6. You want the price for the code typed in F1. The price sits in the third column of the table B2:D6, so the index is 3.

1. Type the code to look up, for example A102, into F1.
2. Enter =VLOOKUP(F1, B2:D6, 3, 0).
3. VLOOKUP scans B2:B6 for A102, finds it in row 4, and returns the value three columns across in D4.
4. Because the fourth argument is 0, a code that is not present returns #N/A rather than a near match.

Looking up A102 with index 3 returns 12.00 from column D.

Why analysts move beyond VLOOKUP

VLOOKUP works, but the col_index_num is a hardcoded integer with no link to the column it points at. The moment someone inserts a column inside the table, the index still says 3 and now points at the wrong field, returning a plausible but wrong number that no error flag catches.

For this reason most modelers replace VLOOKUP with INDEX and MATCH or, on current Excel, with XLOOKUP. Both reference the return column directly, survive inserted columns, and can look to the left of the key.

• =INDEX(D2:D6, MATCH(F1, B2:B6, 0)) returns the same price and breaks far less often.
• =XLOOKUP(F1, B2:B6, D2:D6) is the modern one-function equivalent and defaults to exact match.
• Both alternatives let the lookup key sit anywhere, not only in the leftmost column.

Common pitfalls in models

The default approximate match is the quiet killer. If you omit the fourth argument on an unsorted join, VLOOKUP returns the last value it passed before overshooting, which looks like data, not an error. Always write 0 explicitly.

Mismatched types also bite: a lookup value stored as text "1001" will not match a number 1001 in the table, returning #N/A even though they look identical. Lock the table_array with absolute references like $B$2:$D$6 before copying the formula down, or the range will drift.