Excel VLOOKUP practice

The guide · VLOOKUP / XLOOKUP

What VLOOKUP does

VLOOKUP finds a value in the first column of a table and returns something from the same row. =VLOOKUP(D2, A2:B6, 2, FALSE) means: find what's in D2 in the first column of A2:B6, and return the value from column 2 of that table.

Syntax

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

• lookup_value: what you're looking for. Point at a cell, don't type it in.
• table_array: the table. The value you search for must be in its first column.
• col_index_num: which column of the table to return, counting from 1.
• range_lookup: use FALSE for an exact match. Almost always what you want.

Why FALSE matters

If you leave the last argument out, VLOOKUP does an approximate match. That's meant for sorted number bands like tax brackets. On an unsorted list of names it can return the wrong row without any error, which is the worst kind of mistake. Write FALSE.

XLOOKUP: the modern version

In Excel 365 and 2021, =XLOOKUP(D2, A2:A6, B2:B6) does the same job. It looks in one column and returns from another, does an exact match by default, works to the left, and doesn't break when someone inserts a column. Both are accepted in this challenge.

Common mistakes

• #N/A: the value wasn't found. Check spelling and stray spaces, or wrap the formula in IFERROR.
• Typing the name into the formula. =VLOOKUP("Cheese", …) only ever finds cheese. Point at D2 so it works for any product.
• A table that moves when copied. Lock it as $A$2:$B$6 before filling the formula down.

FAQ

How do I use VLOOKUP in Excel?

Write =VLOOKUP(value, table, column number, FALSE). For example =VLOOKUP(D2,A2:B6,2,FALSE) finds D2 in column A and returns the matching value from column B.

Why does VLOOKUP return #N/A?

The lookup value was not found in the first column of the table. Check spelling, extra spaces and that you used FALSE for an exact match.

Should I use VLOOKUP or XLOOKUP?

If you have Excel 365 or 2021, XLOOKUP is simpler and safer. VLOOKUP works in every version, so it is still worth knowing for interviews and older files.