What does the 4th argument in VLOOKUP mean?

FALSE means exact match. TRUE, or leaving it out, means approximate match on sorted data. For IDs and names use FALSE.

Why is my VLOOKUP returning

The key is not in the first column of the table, or it differs by a space or a type. Check for trailing spaces and numbers stored as text.

Can VLOOKUP look to the left?

No. The key must be in the leftmost column of the table. Use INDEX and MATCH or XLOOKUP instead.

What is better than VLOOKUP?

XLOOKUP, in Excel 2021 and Microsoft 365. It defaults to exact match, searches any column, and has a built-in not-found value.

Function guides · Lookup

VLOOKUP function

Finds a value in the first column of a table and returns a value from another column in the same row.

Syntax

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

Argument	What it is
`lookup_value`	The value to find in the first column of the table.
`table_array`	The table. The lookup column must be its leftmost column.
`col_index_num`	Which column of the table to return, counting from 1.
`range_lookup`	FALSE for an exact match. TRUE or omitted for an approximate match on sorted data.

What VLOOKUP does

VLOOKUP looks down the first column of a table for a value and returns something from the same row. It is old, it has quirks, and it is still on nearly every intermediate Excel test.

The V stands for vertical. You give it the key to find, the table to look in, and the number of the column to return. The key must sit in the leftmost column of the table, and the column number counts from there.

The last argument is the one that matters. FALSE means find this exact value. TRUE, or nothing at all, means find the nearest smaller value in sorted data, which is a very different thing.

VLOOKUP examples

1. Price from a product code

Price from a product code. The formula =VLOOKUP("B-22",A2:C4,3,FALSE) in cell E2 returns 34.
	A	B	C	D	E
1	SKU	Product	Price
2	A-10	Stapler	12.5		34
3	B-22	Lamp	34
4	C-05	Chair	89

Excel searches the first column of A2:C4 for "B-22", finds it in the second row, and returns the third column of that row, the price. The FALSE at the end asks for an exact match.

2. Approximate match for grade bands

Approximate match for grade bands. The formula =VLOOKUP(83,A2:B6,2,TRUE) in cell D2 returns B.
	A	B	C	D
1	Min	Grade
2	0	F		B
3	60	D
4	70	C
5	80	B
6	90	A

With TRUE as the last argument, VLOOKUP finds the largest value in the first column that does not exceed the lookup value. The table must be sorted ascending. A score of 83 lands on the band that starts at 80.

This is the one legitimate use of approximate matching: bands such as grades, tax brackets and shipping tiers.

3. Copying down with a locked table

Copying down with a locked table. The formula =VLOOKUP(D3,$A$2:$B$4,2,FALSE) in cell E3 returns 12.5.
	A	B	C	D	E
1	SKU	Price		Order	Price
2	A-10	12.5		C-05	89
3	B-22	34		A-10	12.5
4	C-05	89

When the formula is filled down a column of keys, the table range has to stay put. Dollar signs lock it, so row 2 and row 3 both look at $A$2:$B$4. The key reference, D3 here, is deliberately left relative.

Common VLOOKUP mistakes

Leaving off FALSE. Without it, VLOOKUP does an approximate match and can return a neighboring row's value with no error. Type FALSE unless you are matching sorted bands.
Forgetting the dollar signs. Fill the formula down and the table range slides with it, so lookups at the bottom miss. Lock it: $A$2:$C$4.
#N/A. The value is not in the first column, or it differs by a trailing space or a number stored as text. Check with TRIM and compare types.
Looking left. VLOOKUP cannot return a column to the left of the lookup column. Use XLOOKUP, or INDEX and MATCH.
Hard-coded column numbers. Insert a column in the table and the 3 now points at the wrong field. Use MATCH for the column index, or XLOOKUP.

VLOOKUP on Excel job tests

The usual prompt gives two tables and asks you to bring one field across by an ID. Four things decide whether it works: FALSE for exact match, a locked table range, the right column number, and a clean key.

Count columns inside the table range, starting at 1 for the key column. A lot of wrong answers come from counting from the sheet's column A. Then check the first and last rows by eye; a range one row short misses the final record.

If the prompt asks for a message when the ID is not found, add IFERROR. If it does not, leave the error visible, because a missing key is information. Where XLOOKUP is allowed it is shorter, but know VLOOKUP anyway.

VLOOKUP questions

What does the 4th argument in VLOOKUP mean?: FALSE means exact match. TRUE, or leaving it out, means approximate match on sorted data. For IDs and names use FALSE.
Why is my VLOOKUP returning: The key is not in the first column of the table, or it differs by a space or a type. Check for trailing spaces and numbers stored as text.
Can VLOOKUP look to the left?: No. The key must be in the leftmost column of the table. Use INDEX and MATCH or XLOOKUP instead.
What is better than VLOOKUP?: XLOOKUP, in Excel 2021 and Microsoft 365. It defaults to exact match, searches any column, and has a built-in not-found value.

Practice VLOOKUP

Try it in a working grid: the free lesson VLOOKUP: find a value by its key (module 11 of Everyday Excel) has checked VLOOKUP exercises.

Practice this function