XLOOKUP
Excel Tips and Tricks

XLOOKUP and XMATCH Function Step by Step Tutorial

In this article, You will learn how to use XLOOKUP and XMATCH Function in Microsoft Excel. XLOOKUP is very powerful functions. This function is currently available to Microsoft 365 subscribers in Current Channel of Office Insider. XLOOKUP can be used in place of VLOOKUP, HLOOKUP, INDEXMATCH etc. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches. It can lookup in vertical and horizontal both type of ranges.

XLOOKUP

Syntax

=XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments

  • lookup_value – The lookup value.
  • lookup_array – The array or range to search.
  • return_array – The array or range to return.
  • If_not_found – [optional] Value to return if no match found.
  • match_mode – [optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match.
  • search_mode – [optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending.

Watch the step by step video tutorial:

Below are the few examples of XLOOKUP

Example #1: XLOOKUP in place of VLOOKUP

We can use XLOOKUP in place of VLOOKUP. As given in below image to get the Sale Amount on the base of EMP Id, we can use

=XLOOKUP(G15,A:A,C:C)
XLOOKUP in place of VLOOKUP
XLOOKUP in place of VLOOKUP

 

Example #2: No need to use IFERROR in XLOOKUP

We use IFERROR with VLOOKUP to handle the potential error, which may occur if lookup value not found in lookup array. XLOOKUP has an additional argument “[If_not_found]” in its syntax. You can use some text here so it will return that text in place of error.

 =XLOOKUP(G15,A:A,C:C,"")

 

No need to use IFERROR in XLOOKUP
No need to use IFERROR in XLOOKUP

 

Example #3: Approximate match with XLOOKUP

XLOOKUP provides the 4 type of match mode –  exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match. In the blow example, we have used to create the sales bucket

=XLOOKUP(C2,$H$5:$H$10,$I$5:$I$10,"",-1)
Approximate match with XLOOKUP
Approximate match with XLOOKUP

 

Example #4: Wildcard with XLOOKUP

In the match mode, 4th option is for Wildcard. In the below given image we have used wildcard in XLOOKUP to get the sales by partial employee name-

Learn how to use Wildcard in Microsoft Excel

=XLOOKUP(F5,B:B,C:C,"",2)
Wildcard with XLOOKUP
Wildcard with XLOOKUP

 

Example #5 XLOOKUP in place of INDEX – MATCH

Unlike VLOOKUP, we have used XLOOKUP form Right to Left. So we don’t need to use INDEX and MATCH function. In the below image we have used XLOOKUP to get the Sales amount on the base of EMP ID

=XLOOKUP(G6,C:C,A:A)
XLOOKUP in place of INDEX - MATCH
XLOOKUP in place of INDEX – MATCH

 

Example #6 XLOOKUP in place of HLOOKUP

XLOOUP works in both direction – Vertically and Horizontally. So in place of HLOOKUP we can use XLOOKUP. In the below given image, we have used XLOOKUP to get the Sales for Months

=XLOOKUP(F6,$A$1:$D$1,$A$2:$D$2)
XLOOKUP in place of HLOOKUP
XLOOKUP in place of HLOOKUP

Learn how to use HLOOKUP in Excel:

Example #7 Search from Last to First

In the XLOOKUP, we can search from last to first also. If you have duplicate values in your lookup arrays, then you can get the result for last occurrence also. In the below image we have used search mode as -1, which is for “search last to first

=XLOOKUP(I6,C:C,D:D,"",0,-1)
Search from Last to First
Search from Last to First

 

XMATCH

XMATCH is same as MATCH function but it is more powerful than Match function. supports approximate and exact matching, wildcards (* ?) for partial matches. It can lookup in vertical and horizontal both type of ranges.

Syntax

=XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])

Example:

In the below image, we have used XMATCH to get the row number, wherein Sales Amount is exact 198 or next smaller of 198.

=XMATCH(G5,C:C,-1)

We can use the wildcards with XMATCH. To get the row number for an employee on the base of Partial name we can use-

=XMATCH(G9,B:B,2)

We can match for the last also. We have duplicate values in lookup array, we can use search mode as last to first

=XMATCH(G14,B:B,0,-1)
XMATCH function
XMATCH function

 

Click here to download the practice file.

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com