Coupler.io Blog

Excel XLOOKUP Function Explained – Syntax and Examples

  • Zakhar Yung
    • Excel XLOOKUP

    VLOOKUP in Excel allows you to search for a match for a specified lookup value to the right of it. If you need to make a reverse lookup – to the left – the XLOOKUP function in Excel will do the job. 

    However, it is not the only direction this function can cover. Read on to explore further details.

    XLOOKUP in Excel – VLOOKUP, HLOOKUP and LOOKUP in one function 

    The first thing you should know about the XLOOKUP function is that it is only available in Microsoft Excel Online and Microsoft 365.

    XLOOKUP is the function that provides a two-way lookup: 

    Excel XLOOKUP function syntax

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

    Excel XLOOKUP formula example

    Let’s check out how XLOOKUP works in the example of a simple lookup without optional arguments. We have a dataset that we loaded to Excel using Coupler.io, a reporting automation solution. 

    The XLOOKUP function will help us find the name of the owner of an Acura in the first columns. Here is the formula:

    =XLOOKUP("Acura",D2:D50,A2:A50)

    XLOOKUP examples with optional arguments

    Now let’s look at more advanced examples of Excel formulas with this advanced lookup function using optional parameters.

    Excel XLOOKUP with match_mode argument

    The [match_mode] is the fifth parameter in the XLOOKUP formula. It is denoted as a numeral from -1 to 2 which correspond to the following match types:

    With a wildcard match, you need to use wildcards (*, ?, or ~) when specifying a lookup value. They have the following meanings:

    Wildcards only work with textual values

    As an example, let’s lookup an approximate match – find the name of the owner of the car with the brand name starting with “M”. Here is the XLOOKUP formula:

    =XLOOKUP("M*",D2:D50,A2:A50,,2)

    The formula returned “Randolf” who drives a Mitsubishi and is the first match from the top. Now, what if you want to look up the first match from the bottom? In this case, choose a respective search mode.

    Excel XLOOKUP search mode

    The [search_mode] is the sixth parameter in the XLOOKUP formula. It is denoted as a numeral from -2 to 2 with 0 excluded:

    As an example, let’s find the name of the owner of the car with the brand name starting with “M“. This time, the lookup will start from the bottom. Here is the XLOOKUP formula:

    =XLOOKUP("M*",D2:D101,A2:A101,,2,-1)

    How to use XLOOKUP with multiple criteria

    The more criteria you have, the more accurate the xmatch is, right? For example, in our data set, there are multiple Mitsubishi owners and we need to look up the one who drives a violet Mitsubishi. So, we have two criteria (lookup values) to search for a match. XLOOKUP allows you to do this with the following syntax:

    =XLOOKUP("lookup_value1"&"lookup_value2"&...,lookup_array1&lookup_array2&..., return_array, "[if_not_found]", [match_mode], [search_mode])

    The only difference in syntax between XLOOKUP with one criterion and XLOOKUP with multiple criteria is that you need to add the required lookup values and lookup arrays using the ampersand (&).

    Here is an example of the XLOOKUP formula with multiple criteria:

    =XLOOKUP("Mitsubishi"&"Violet",D2:D101&E2:E101,A2:A101)

    Is there any XLOOKUP vs. INDEX MATCH speed difference?

    In our Excel VLOOKUP tutorial, we mentioned the combination of the INDEX and MATCH functions to implement a reverse vertical lookup. Basically, this is a workaround for those Excel users who do not have XLOOKUP. Which option is more efficient?

    We tested both on a dataset with 192,000 cells, however, we did not notice any difference in speed. Here are the formulas we used:

    XLOOKUP:

    =XLOOKUP("Skoda",D:D,A:A)

    INDEX+MATCH:

    =INDEX(A:A,MATCH("Skoda",D:D,0))

    For larger datasets, it is likely that XLOOKUP will be more efficient if you use a binary search mode. The syntax of XLOOKUP is also much simpler compared to INDEX+MATCH. However, if you don’t have access to XLOOKUP, then INDEX and MATCH should definitely be your next choice.

    What does XLOOKUP for a horizontal lookup look like?

    As a final word, let’s show how you can use XLOOKUP to look up horizontally, not just vertically. We have transposed our dataset in our worksheet, so now it looks as follows:

    And here is the XLOOKUP formula to search horizontally for a Toyota owner:

    =XLOOKUP("Toyota",B4:CW4,B1:CW1)

    How to get XLOOKUP function if you don’t have it in your workbook

    Users of Microsoft 365 already have access to XLOOKUP without taking any additional actions. For some older versions of Excel, such as Home, Personal, or University, you need to join the Office Insider program as follows:

    XLOOKUP cannot be implemented in Excel 2019 or earlier versions. However, you can use Excel Online instead. All you need to do is drop your Excel files in the OneDrive folder on your PC and sign into OneDrive on the web. Here is how your OneDrive folder will look in your internet browser:

    Now you can open your files in Excel Online and enjoy the power of XLOOKUP. 

    Automate data import from cloud sources to Excel

    In this Excel tutorial, we mentioned Coupler.io as a solution for importing data to Excel. It lets you connect 60+ cloud data sources and automate data exports on a schedule to your workbook.

    You can try it yourself for free right away – select the needed source application in the form below and click Proceed. 

    You’ll be offered to create a Coupler.io account for free. 

    After that configure the connection of your source application and choose the Excel workbook and worksheet for your data.

    As the final step, set up a schedule for automatic data refresh to always access the recent data from your source in Excel.

    In addition to Excel, Coupler.io supports other destination apps including Power BI, Google Sheets, Looker Studio, and more.

    That’s it! Master your Excel skills and good luck with your data!

    Exit mobile version