XLOOKUP Deep Dive
Everything VLOOKUP could do, and several things it couldn't — the modern standard for lookups.
Compatibility: Microsoft 365, Excel 2021+
What You'll Learn
- How XLOOKUP improves on VLOOKUP's handling of missing matches
- Why XLOOKUP can look in either direction, unlike VLOOKUP
- How XLOOKUP can return more than one column of data at once
XLOOKUP searches a range for a value and returns a corresponding result from another range:
=XLOOKUP(A2,ProductID,ProductName,"Not Found")
Three Advantages Over VLOOKUP
First, XLOOKUP has a clean, built-in way to handle a missing match (the 4th argument above) — no separate IFERROR wrapper needed. Second, it can look in either direction — the return range doesn't have to be to the right of the lookup range, unlike VLOOKUP's strict left-to-right requirement. Third, it's not thrown off by inserting or deleting columns between the lookup and return ranges, since it references whole columns/ranges directly rather than counting a column index number.
Exact Match by Default
Unlike VLOOKUP, XLOOKUP defaults to an exact match — there's no easy-to-forget FALSE argument required to get the reliable behavior you almost always want.
Returning Multiple Columns at Once
XLOOKUP can return more than one column in a single formula by selecting a multi-column return range — e.g. =XLOOKUP(A2,ProductID,ProductTable[Name]:ProductTable[Price]) can pull back both the name and price together.
Common Mistakes
- Using XLOOKUP in a workbook that might be opened in an older Excel version that doesn't support it
- Forgetting XLOOKUP still needs matching-length lookup and return ranges
Practice Exercise
Rebuild the VLOOKUP practice exercise (product ID/name/price lookup table) using XLOOKUP instead, including a fallback value for IDs that don't exist.
Key Takeaways
- XLOOKUP has a built-in fallback for missing matches
- XLOOKUP can look left or right, unlike VLOOKUP
- XLOOKUP defaults to an exact match, with no FALSE argument needed