VLOOKUP vs XLOOKUP

First principles. A lookup answers a question of the form "in that reference range, find the row where the key matches mine, and hand me back the value sitting in a particular column." For two decades the tool for this was VLOOKUP, and it carried three design scars that cost analysts real money.

What is wrong with VLOOKUP?

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) fails in three structural ways. First, it can only look rightward. The key must be the leftmost column of table_array, and the value returned must sit to its right. Want the product name that's to the left of a SKU column? VLOOKUP simply can't do it. Second, the return column is a hardcoded integer. col_index_num is a number like 4, meaning "the 4th column". Insert a new column anywhere inside the table and every VLOOKUP pointing past it now returns the wrong column, with no error to tell you. Third, and most dangerous, its fourth argument defaults to approximate match. Leave out range_lookup and Excel assumes TRUE, approximate, which on unsorted data returns confident nonsense. It's the deadliest default in the application, and it gets a whole common mistake of its own in Common mistakes.

How does XLOOKUP work?

XLOOKUP repairs all three. Here's its signature:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value, the key you are matching.
• lookup_array, the single column (or row) you search in.
• return_array, the single column (or row) you fetch from. Because the search range and the return range are separate arguments, the return can sit on either side of the key, or even on another sheet. Inserting columns between them breaks nothing.
• if_not_found, what to return when there's no match. It replaces the IFERROR wrapper.
• match_mode, 0 exact (the default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
• search_mode, 1 first-to-last (default), -1 last-to-first (reverse), 2/-2 binary search on sorted data.

Look at what the defaults now do for you. Exact match, first hit, error if absent. That's the safe behaviour you'll want 90% of the time, and you get it without touching a single optional argument. VLOOKUP made you opt in to safety by remembering to type FALSE. XLOOKUP makes you opt out of it, and that flip is why you switch.

Example: looking up a price with XLOOKUP

Start small. Say a price list is set up as a Table, with product codes in A2:A50 and unit prices in C2:C50. To fetch the price for the code in F2:

=XLOOKUP(F2, A2:A50, C2:C50)

You don't count columns, the key doesn't have to sit on the left, and you can't fall into approximate match by accident. If F2 holds a code that isn't in the list, you get #N/A. That's a loud and correct signal. The exact match and if_not_found section shows you how to turn it into something a colleague can act on.