VLOOKUP, INDEX/MATCH, XLOOKUP — which to actually use

If you are writing a lookup today and your Excel supports it, use XLOOKUP. Everything below is context for why, and what to do when you meet the older two in someone else's file.

The problem with VLOOKUP

=VLOOKUP(A2, Products!A:D, 3, FALSE)

Three things go wrong with this:

1. The key must be the leftmost column. If it is not, you rearrange the sheet or give up.
2. The 3 is a position, not a name. Insert a column in Products and the formula silently returns the wrong data. No error — just wrong numbers.
3. Forgetting FALSE gives you an approximate match, which on unsorted data returns nonsense.

INDEX/MATCH

=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

Read it inside out: MATCH finds which row, INDEX fetches from which column. The key can be anywhere, and both references are real columns, so inserting a column moves them correctly.

XLOOKUP

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

Lookup value, where to search, what to return. Exact match by default, a built-in "not found" argument, and it searches in any direction.

Learn INDEX/MATCH well enough to read it, because it is everywhere. Write XLOOKUP.