Have you used XLOOKUP yet?

XLOOKUP Release Date

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?
  • Can find the last match
  • Can look to the left
  • Has improved speed
  • No longer relies on Column Number, no more worry about someone inserting/deleting a column in between
  • Performance improvement because you are only specifying two columns instead of the whole lookup table
  • XLOOKUP returns a range instead of VLOOKUP returning a value

    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:

    XLOOKUP Release Date

    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.

    XLOOKUP Release Date

    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.

    XLOOKUP Release Date

    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]
    « PREV
    NEXT »