For decades, VLOOKUP was the king of lookup functions. But it has critical flaws: it can only look to the right, and inserting a new column breaks it.
Microsoft's answer is XLOOKUP, a modern, flexible, and superior replacement available in newer versions of Excel.
Why XLOOKUP is Better
- Looks Left: XLOOKUP can search in any column and return a value from any other column, even to the left.
- No Column Counting: You select the lookup and return columns directly, so inserting new columns won't break your formula.
- Built-in Error Handling: The `[if_not_found]` argument lets you specify what to show if there's no match, eliminating ugly `#N/A` errors.
Example:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode],
[search_mode])
It has more options but is far more intuitive once you start using it.
Struggling with a lookup?
Describe what you need to find, and our AI will build the correct formula for you.
Go to Excel Bot