XLOOKUP function searches a range for a match and returns an item from a second range. It can search both vertically or horizontally. XLOOKUP replaces both VLOOKUP and HLOOKUP.
XLOOKUP in Excel 365
XLOOKUP is a new Excel function introduced in 2020. It is available in Excel 365. XLOOKUP is not available in Excel 2016 or Excel 2019.
The three most popular Excel functions are:
- SUM
- AVERAGE
- VLOOKUP
XLOOKUP replaces the following Excel functions:
- VLOOKUP – vertical search
- 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)
What is it used for?
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 or rows.
It can retrieve a description or a price by looking up a part number. XLOOKUP also can find a grade by giving a student id number. It can retrieve either horizontally or vertically. VLOOKUP can’t do that!
XLOOKUP video tutorial
So, do you need a video tutorial about the new XLOOKUP function? I created this XLOOKUP tutorial.
Try it out yourself
Try the new XLOOKUP function. I think you will like it and add it to your Excel toolkit. It has several advantages over the older functions.
Try to replace a VLOOKUP or an HLOOKUP with the XLOOKUP function. It is a better tool than the older functions.
