The whole purpose of XLOOKUP is to find one result, find it quickly, and return the answer to the spreadsheet.
- Joe McDaid, Excel Project Manager
Before I go into details about XLOOKUP, let me give you the bad news. XLOOKUP isn't available to the general public as of now. It is currently a beta feature, and only available to a portion of Office Insiders at this time. So unless you have enlisted with the Office Insider, you would be able to test the features of XLOOKUP yet, and Microsoft has not yet announced the XLOOKUP release date.
How to join the Office Insider program?
Simple. Open a blank Excel file, click on File -> Account -> Office Insider -> Join Office Insider. That's it! Enjoy the powerful features of XLOOKUP before your colleague does.
Now, coming back to the technicalities of XLOOKUP, I know what you are thinking.
What is XLOOKUP?
Well, it's not just another addition to the family of Excel lookup functions. It is "the" Excel lookup function that you would ever use. Here's what the XLOOKUP syntax looks like:
XLOOKUP(Lookup_Value, Lookup_Array, Results_Array, [Match_Mode], [Search_Mode])
The choices for Match_Mode are:
0 Exact Match (default)
-1 Exact Match or Next Smaller
1 Exact Match or Next Larger
2 Wildcard Match
The choices for Search_Mode are
1 first to last (default)
-1 last to first
2 binary search, first to last (requires lookup_array to be sorted)
-2 binary search, last to first (requires lookup_array to be sorted)
How is it different than a simple VLOOKUP?
How to find the Last Match using XLOOKUP?
XLOOKUP allows you to begin your search at the bottom of the data set. This is great for finding the last match in a data set. You can use this feature as shown below:
How to use XLOOKUP to look to the Left of the data?
Unlike LOOKUP and INDEX/MATCH, there is no hassle looking to the left of the key using XLOOKUP.
How to fetch data from Multiple Columns using XLOOKUP?
XLOOKUP can return data from either one column or return an array of results from multiple columns.
Isn't it great! Now you no longer have to use a bunch of Lookup functions in Excel like VLOOKUP, INDEX/MATCH etc. Simply use XLOOKUP instead.
[P.S. Data used from Kaggle/Sofia Air Quality]
