FormulaFixer / Formula reference / VLOOKUP

VLOOKUP formula

Searches down the first column of a range for a match, then returns a value from a column to the right of it. The most-used lookup formula in spreadsheets, and the most common source of #N/A errors.

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The fourth argument controls how strict the match is: FALSE (or 0) requires an exact match and is almost always what you want; leaving it blank or using TRUE allows an approximate match, which silently returns the wrong row if your data isn't sorted. The other classic gotcha: VLOOKUP can only look to the right — the column you're searching must be the first column in table_array, or you'll get a #N/A even when the value clearly exists.

Examples

=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

Looks up the ID in A2 within Sheet2's column A, and returns the matching value from column B.

=VLOOKUP("Widget",A2:D50,4,FALSE)

Looks up a literal text value directly instead of referencing a cell.

=IFERROR(VLOOKUP(A2,Prices!A:C,3,FALSE),"Not found")

Wraps the lookup in IFERROR so a missing value shows a clean message instead of #N/A.

Paste your own VLOOKUP formula and get a plain-English explanation, or describe what you need and let FormulaFixer write it for you.

Explain a VLOOKUP formula →

More formulas