For decades VLOOKUP was the formula that separated casual spreadsheet users from power users. It also caused endless frustration. XLOOKUP, now in modern Excel and matched by Google Sheets' own tools, fixes nearly every pain point. If your version has it, switch.
Why VLOOKUP hurt
VLOOKUP looks up a value and returns something from a column to its right, counted by number.
`=VLOOKUP(A2, Data!A:D, 3, FALSE)`
Three problems showed up constantly. It cannot look to the left of the search column. The column number is hard-coded, so inserting a column silently breaks it. And forgetting the final `FALSE` returns sloppy approximate matches.
How XLOOKUP fixes it
XLOOKUP takes the lookup value, the column to search, and the column to return, as ranges:
`=XLOOKUP(A2, Data!A:A, Data!C:C)`
Benefits stack up fast:
- Look any direction. The return column can sit left or right of the search column.
- No magic numbers. You point at actual columns, so inserting a column does not break it.
- Exact match by default, so no silent wrong answers.
- Built-in not-found handling. Add a fourth argument like `"Not found"` instead of wrapping the whole thing in IFERROR.
A real example
Suppose names are in column C and IDs in column A, and you have a name but need the ID, a leftward lookup VLOOKUP cannot do:
`=XLOOKUP(F2, C:C, A:A, "No match")`
That single formula replaces a tangle of INDEX/MATCH that beginners dreaded.
When you still need VLOOKUP
If you share files with people on older Excel, XLOOKUP shows an error on their end. For maximum compatibility, INDEX/MATCH remains the universal fallback. Otherwise, XLOOKUP is the cleaner tool, and learning it first will save you years of column-counting headaches.