VLOOKUP

Find a value in the first column of a range and return something from the same row.

Syntax

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

Argument	Required	Description
`lookup_value`	Required	The value to search for. Only ever searched for in the first column of table_array.
`table_array`	Required	The range to search. Its leftmost column is the one that gets searched.
`col_index_num`	Required	Which column of the range to return, counted from 1. A position, not a reference.
`range_lookup`	Optional	FALSE for exact match, TRUE or omitted for approximate. Omitting it is the most common cause of wrong answers.

Examples

Every example below was executed against VisiGrid engine 0.35.0 — not transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.

Formula	Result	Notes
`=VLOOKUP(102,A2:D4,3,FALSE)`	`Bo`	IDs in column A, names in column C — the third column of the range.
`=VLOOKUP(102,A2:D4,4,FALSE)`	`700`	Same lookup, returning the fourth column instead.
`=VLOOKUP(999,A2:D4,3,FALSE)`	`#N/A`	No matching row. Wrap in IFERROR to substitute your own message.
`=VLOOKUP("Ea*",B2:D4,2,FALSE)`	`Ana`	Wildcards work in an exact-match lookup.
`=VLOOKUP("102",A2:B2,2,FALSE)`	`#N/A`	A text key never matches a numeric cell, whatever the spelling. Excel agrees.
`=VLOOKUP("0102",A2:B2,2,FALSE)`	`#N/A`	And no spelling matches — this was pinned when only the canonical one failed.
`=VLOOKUP(VALUE("102"),A2:B2,2,FALSE)`	`hit`	VALUE turns the key into a number, so both sides agree. This is the fix.
`=VLOOKUP(102,A3:B3,2,FALSE)`	`#N/A`	TEXT(...,"@") makes a genuinely text cell, so a numeric key no longer finds it.
`=VLOOKUP(102,A2:B3,2,FALSE)`	`numrow`	A numeric key skips the text row and finds the number.
`=VLOOKUP("102",A2:B3,2,FALSE)`	`textrow`	And a text key finds the text row. A leading apostrophe marks a cell as text.

Always pass FALSE

The fourth argument is optional, and leaving it out does not mean “exact” — it means approximate. On a table that isn’t sorted ascending, an approximate match returns the closest preceding row, which looks like a real answer and isn’t.

=VLOOKUP(102, A2:D4, 3)          ← approximate. Almost never what you want.
=VLOOKUP(102, A2:D4, 3, FALSE)   ← exact. What you meant.

This is the single most common source of wrong numbers in spreadsheets. If you write only one thing from this page into your habits, write FALSE.

The third argument counts columns

col_index_num is a position, not a reference. 3 means “the third column of this range” — so inserting a column anywhere inside the range changes what that number points at, and the formula carries on returning a value from the wrong place without erroring.

Change the column index below and watch the answer change — no error, just a value from somewhere else. That is what happens when someone inserts a column into the range:

Edit the column index. 3 is the rep, 2 is the region, 4 is the sales figure.

ID	Region	Rep	Sales
101	East	Ana	500
102	West	Bo	700
103	East	Cy	300

Look up ID:	102	Column index:	3
Result:	Bo

Edit the highlighted cell and the formula recalculates — computed by VisiGrid engine 0.35.0, in your browser. Nothing is saved.

That fragility is structural and it is why XLOOKUP exists. If you are choosing for new work, choose XLOOKUP.

It only ever searches the leftmost column

VLOOKUP searches the first column of table_array and nothing else. If the value you’re matching on sits in column C, the range has to start at column C. And if the value you want back is to the left of the one you’re searching, VLOOKUP cannot express that at all — use XLOOKUP, or INDEX with MATCH.

If it isn’t returning what you expect, the six usual causes cover almost every case.

Excel compatibility

Matches Excel for exact match, approximate match, the omitted fourth argument, wildcards and the no-match case. A text lookup value never matches a numeric cell, and a numeric one never matches a text cell — in either direction and whatever the spelling, as in Excel.