XLOOKUP is an Excel function that can lookup and reference data. Since it can search both vertically and horizontally, it can replace both VLOOKUP and HLOOKUP.
XLOOKUP in Excel 365
XLOOKUP is a new Excel function introduced in 2020. It is available in Excel 365. It is not available in previous versions of Excel.
The three most popular Excel functions are:
- SUM
- AVERAGE
- VLOOKUP
XLOOKUP replaces the following Excel lookup functions:
- VLOOKUP: vertical searches
- HLOOKUP: horizontal search
- INDEX and MATCH: column and row search
XLOOKUP replaces VLOOKUP
VLOOKUP debuted in Excel 1.0 in 1985. It copied the old Lotus 1-2-3 function @VLOOKUP. VLOOKUP runs billions of Excel spreadsheets. But now VLOOKUP is outdated. XLOOKUP is better than VLOOKUP.
Excel users can replace VLOOKUP and HLOOKUP with XLOOKUP for all new spreadsheets. Here are some benefits of XLOOKUP over VLOOKUP:
- easier to use
- simpler to understand
- “not found” does not create an error
- horizontal search: top to bottom and bottom to top
- vertical search: left to right and right to left
Learn Excel: Ultimate Guide to Learn Excel Basics
XLOOKUP Syntax
XLOOKUP searches a range for a match and returns an item from a second range. By default, it uses an exact match.
XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- Lookup_value: value to search for
- Lookup_array: range to search
- Return_array: range to return
- If_not_found: text returned if no match is found. [Optional]
- Match_mode: specify how to match lookup_value against the values in lookup_array [Optional]
- 0 – exact match (default)
- -1 – exact match or next smaller item
- 1 – exact match or next larger item
- 2 – wildcard character match
- Search_mode: specify the search mode to use [Optional]
- 1 – search first to last (default)
- -1 – search last to first
- 2 – binary search (sorted ascending order)
- -2 – binary search (sorted descending order)
XLOOKUP searches a range for a match and returns the corresponding item from a second range. Unlike VLOOKUP, it uses an exact match. This solves a weakness in VLOOKUP. Also, it can search both columns and rows.
It can retrieve a description or a price by looking up a part number. XLOOKUP also find a grade by giving a student ID number. It can retrieve either horizontally or vertically. VLOOKUP can’t do that!
XLOOKUP tutorial
Here is an XLOOKUP tutorial video:
Jeff Mankin teaches financial literacy and Excel. He is the founder of Finally Learn.
