XLOOKUP Function in Excel
Intermediate
A modern replacement for VLOOKUP, HLOOKUP, and LOOKUP. Searches a range or an array, and returns an item corresponding to the first match it finds.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Example
Use Case
Find an employee's name based on their ID, searching from the bottom of the list.
Common Errors to Avoid
- Not available in older Excel versions (requires Microsoft 365 or Excel 2021+)
- Lookup and return arrays must be the same size
- Forgot to specify if_not_found parameter, resulting in #N/A errors
Pro Tips
Use XLOOKUP instead of VLOOKUP for new spreadsheets—it's more flexible and can search left or right. Always specify a custom if_not_found message for better user experience.
Need help with this formula?
Our AI Formula Generator can write this for you instantly.
Try AI Formula Generator →