Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • OneDrive
      • Google Docs
      • Google Sheets
      • Google Drive
    • Email
      • Gmail
      • Outlook
      • Emal Etiquette
    • Office Etiquette
    • Productivity
    Inside The Web
    Home»Office»Excel»How to Use XLOOKUP in Excel

    How to Use XLOOKUP in Excel

    Nisha GurungBy Nisha GurungAugust 25, 2023 Excel 13 Mins Read

    Excel’s XLOOKUP function beats all the other lookup functions in terms of features and functionality. It is by far the best alternative to VLOOKUP, HLOOKUP, INDEX and MATCH functions. 

    I know when you look into such big formula at first, you may get intimidated not knowing where to start. But, I’ll guide you through the examples of how to use this function from a  very beginner to an advanced level. You can also find tips and tricks to avoid errors.

    NOTE: As of now only the Excel 2021, 365, and web users can access the XLOOKUP function.

    Benefits of Using XLOOKUP Functions

    • The return value is not just limited to one item. XLOOKUP can return a number of arrays from the lookup table. 
    • The function can look for values on both the right and left of the lookup value. So, the lookup value can be in any row or column.
    • You can use XLOOKUP to search values in both vertical and horizontal arrays.
    • XLOOKUP can perform a binary, reverse, or default start search.
    • When the value is not found, you can enter a “Message” to return to instead of the #N/A error. So you wouldn’t have to use the ISERROR or IFERROR for this. 
    • XLOOKUP supports multiple criteria.

    Tips to Use the XLOOKUP Function

    • To conduct Binary Search Modes, your texts must be sorted in A to Z and Z to A order. For the numbers, they should be either in ascending or descending order. Else, you’ll get an error.
    • I recommend you enter the if_not_found in every XLOOKUP formula to avoid receiving errors.
    • If you need to type a Text in the formula argument, enclose them within the “” (Double Quotation).
    • XLOOKUP function is not at all case-sensitive. 
    • Avoid having unwanted spaces and characters in the Lookup value.
    • You can use Named Ranges or Named Table in the formula to shorten the formula length.

    Arguments Used in the XLOOKUP Function

    Excel’s XLOOKUP function has a lot of arguments compared to the other lookup functions. However, all the arguments are not compulsory. Some of them are optional too.

    Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
    • lookup_value: Item you want to search
    • lookup_array: Array that contains the Lookup value.
    • return_array: Array to return from the lookup_array
    • [if_not_found]: Text to return when the lookup item is missing.
    • [match_mode]: Specify how you want to return the lookup value.
      • 0: Exact Match or #N/A when no value found.
      • -1: Exact Match or closest smaller value when value not found. 
      • 1: Exact Match or next larger value when value not found.
      • 2: Wildcard Match.
    • [search_mode]: Determine the search mode.
      • 1: Start Search from the first value.
      • -1: Reverse Search. Start looking for value from the last value.
      • 2: Binary Search in ascending order.
      • -2: Binary Search in descending order.
    NOTE: The arguments you see inside [] parentheses are completely optional.

    Applications of XLOOKUP Function

    Example 1: Vertical and Horizontal Lookup

    Firstly, we will just take a look into a simple example that illustrates XLOOKUP can return values Vertically as well as Horizontally. Here, we won’t go into depth with all the function arguments as I’ve discussed them one by one in other examples. I’ll just use the lookup value, lookup array, and return array to demonstrate this. 

    Example: Vertical Lookup

    Let’s find out the Total Sales of Tim. For this, we used the formula as

    =XLOOKUP(H2, A2:A5, E2:E5)

    In the XLOOKUP, our Lookup value is H2, the lookup array is A2:A5, and the return array is E2:E5. Since our return array is a column, the formula looked for items vertically and resulted in 4482.

    Example: Horizontal Lookup

    Here, we have a table in the horizontal format. From the data, we will check the Task Status of the employee name “Steven.”  

    =XLOOKUP(A7, B1:G1, B4:G4)

    The above formula looked for Steven horizontally in the B1:G1 array and returned Task Status “Pending” from B4:G4. 

    Example 2: Exact Match

    Using the XLOOKUP function, you can return the exact match from the lookup table. For this, we will pass down the 0-match mode in the formula. 

    In case, there are no values to return, you’ll receive a #N/A error. However, if you specify a message in the “if_not_found,” the formula will return that message instead of the error.

    Example: Suppose, I have a table of Household Expenses. Let’s say I want to find out the Grocery amount for the month of May.

    For this, I will enter the XLOOKUP formula as below. 

    =XLOOKUP(J3,A3:A14, F3:F14, "No Amount", 0)

    In the formula, our lookup_value is Grocery which lies in the lookup_array A3:A14. Similarly, our return_array is F3:F14. If there’s no exact match, we have specified the formula to return “No Amount.” Since we passed down 0 as match mode, the formula will return the exact item from the return array. As a result, the formula returned 227. 

    In case, there was no value, it would’ve returned “No Amount.”

    Example 3: Approximate Match

    For some data in Excel, an approximate match is more helpful than the error or if not found message. To return the approximate match, you can use either -1 or 1 match mode in the XLOOKUP function. We’ll discuss both of them in the example below.

    Example: Let’s say I want to return the discount percentage for the products based on their Unit Price. As there’s no exact value for Unit Price, I need to return the approximate match. 

    ExampleFormulaOutputDescription 
    Exact Match =XLOOKUP(C2, $A$9:$A$11, $B$9:$B$11, ,1)5%Since there’s an exact match in the lookup value, it returned 5%. 

    It implies even though you enter a partial match, the formula prioritizes and returns an exact item.  
    Approximate Large Number (1)=XLOOKUP(C3, $A$9:$A$11, $B$9:$B$11, , 1)30%In this case, the lookup value does not match the item in the lookup array. 

    Here, we have specified the formula to return the closest large number.

    As a result, XLOOKUP returned 30% for the 125 unit price.
    Approximate Small Number (-1)=XLOOKUP(C4, $A$9:$A$11, $B$9:$B$11, , -1)20%Again, we have the same lookup value as in example 2.

    But, this time, we passed down -1 match mode in the formula.

    So, the XLOOKUP formula returned the nearest smallest percentage which is 20%.
    NOTE: At first, the percentage values might return in the original General format. You can convert the value into the percentage later from the Number tab.

    Example 4: Partial Match

    In the XLOOKUP function, you can use the Wildcard Characters to return the lookup values in a partial match. Remember, the Match_Mode 2 in the function argument? It returns the Wild card match.

    There are three different Wild Characters and each of them represents their own meaning.  If you’re a beginner, Wildcard can seem very complex at first. So, before you dive into the examples, I suggest you go through what each Wildcard Characters implies first.

    P.S. In the given examples, we haven’t specified the search mode in the formula. So, the XLOOKUP formula will use the default Start search mode and return the first occurrence from the lists. We will discuss the different search Modes in more detail later with examples. 

    Wildcard CharacterFormulaOutputDescription
    ?=XLOOKUP(“????y”, A4:A9, C4:C9, “No Service”, 2)HaircutIn the XLOOKUP formula, we specified the “????y” as the look-up value.

    Now, if you see at the table there are two names that have the same number of letters and end with “y”. Since XLOOKUP looked from first, here, we got result for Farly.

    But, if you enter “C???y” as the lookup value, it would’ve returned Gel Nails.
    *=XLOOKUP(“B*”, A4:A9, C4:C9, “No Service”, 2)Gel NailsSince, our Lookup value is “B*”, it’ll return the Service for a customer whose name begins with B.

    In case, you passed down “*B” as the lookup value, it would have returned the service for the customer with the last letter as B. For example, “Pedicure” for Bob.
    ~=XLOOKUP(“~*B”, A4:A9, C4:C9, “No Service”, 2)FacialIn the formula, we’ve used the Tile in the lookup value to stop *B from functioning as Wildcard Character.

    As a result, it returned the service for *B value which is “Facial.”

    Example 5: Different Search Modes

    If you’ve used VLOOKUP or HLOOKUP before, you must have noticed these functions returns the first occurrence in duplicate data by default. But, this is not the case in XLOOKUP. You can actually specify the search mode to look for values from last, first, ascending, or descending order. 

    Example: Here, we have a list of Companies with duplicate countries. Let’s return the Company name for each Country.

    Search ModeFormulaOutput Description
    1 (Start Search)=XLOOKUP(D3, B3:B20, A3:A20, “No Company”, 0, 1)Hahn-ReynoldsIn the formula, 1 search mode will return the first instance from the duplicate value. 

    So, for the UK country, we got Hahn-Reynolds.
    -1 (Reverse Search)=XLOOKUP(E4, B3:B16, A3:A16, “No Company”, 0, -1)Welch GroupThis time, we’ve entered -1 Search Mode in the XLOOKUP.

    The formula returned the last item which is “Welch Group” for Spain.
    2 (Ascending Order)=XLOOKUP(E5, B3:B16, A3:A16, “No Company”, 2)Bogisich LLCIf your Data is sorted in the ascending or A to Z order, use the 2 Search Mode.

    Again, just like the Start search, it also returns the first occurrence of the smallest number or first letter item. 
    -2 (Descending Order)=XLOOKUP(B28, B18:B31, A18:A31, “No Company”, 0, -2)Hegmann-LefflerHere, we sorted the data in Z to A order first. 

    Since we passed down -2 in the search mode, it returned the final value of Russia. It is similar to the Reverse Search but the data must be in descending or Z to A order.
    NOTE: If you do not specify the Search Mode, the default will be 1.

    Example 6: Return Array

    One of the specialties of the XLOOKUP function is that it can return multiple items from the lookup table. So, in this example, we’ll explore how to use the XLOOKUP to return an array.

    Example: Let’s say, I have a record of Employee, Title, and Sales amount. Based on the Total Sales, I want to find out the Employee and Title.

    To do so, I used the XLOOKUP formula as below.

    =XLOOKUP(C3, C2:C8, A2:B8, "No Value", 0, 1)

    In the formula, I have passed down the argument to return the A2:B8 array(Employee and Title) for the C3 lookup value. So, I got Margret and Junior Sales as an outcome. If there was no exact match, it would have returned “No Value.”

    Example 7: XLOOKUP Across Sheets

    You can also use the XLOOKUP to extract an item from a different sheet. So, you don’t have to worry about transferring information to another sheet just to use the Lookup table. The best part is it creates a dynamic sheet reference between the worksheets.

    Formula: = XLOOKUP(lookup_value, SheetName!lookup_array, SheetName!return_array, [if_not_found], [match_mode], [search_mode])

    Example: In a New sheet, let’s say I want to prepare a summary of Expenses. For this, I want to extract the information from each sheet before adding them. I entered the following formula.

    =XLOOKUP(B3, Education!B2:F2, Education!B7:F7, "No Value", 0)

    Here, the formula resulted in 630 for May.

    While you can enter the formula manually, it’s a lot easier if you head to the source sheet and select the lookup and return array by yourself. Follow these tips to use the formula.

    1. In a new cell, enter =XLOOKUP(. Click on the lookup value cell and add a Comma.
    2. Head to the Source Sheet, and select the lookup array. Again, type a Comma. 
    3. Now, select the Return array and enter a comma.
    4. Type in the if not found, match mode, and search mode arguments separated by a comma. Once you’re done, Close the bracket and press Enter.

    Example 8: XLOOKUP with Different Workbooks

    We’ve discussed the ways to use XLOOKUP with various conditions and criteria in the sheet from the above examples. But, there can be instances when you have information in the different workbooks and need to draw out data from there, right? To use the XLOOKUP to your utmost advantage, let’s use this function in separate workbooks.

    Formula: = XLOOKUP(lookup_value, [Workbook.xlsx]SheetName!lookup_array, [Workbook.xlsx]SheetName!return_array, [if_not_found], [match_mode], [search_mode])

    With all the formula arguments, sheets, and workbook names, the XLOOKUP function looks even more lengthy and complex. But, I’ll show you the easiest way to enter them. Before you start, open both source and destination workbooks. 

    Example: Let’s take, for instance, I have data about Entertainment expenses in the “Budget” Sheet of the “Expense” workbook. I need to draw out the cost of Movie expenses for all 5 months in the “XLOOKUP” workbook. 

    I used the given formula to achieve this.

    =XLOOKUP(A10, [Expenses.xlsx]Budget!$D$48:$D$50, [Expenses.xlsx]Budget!$E$48:$I$50, , 0)

    The formula returned an entire amount for the month of Jan, Feb, Mar, Apr, and May for Movie. 

    Here’s how I entered the formula.

    • On the Destination workbook, enter =XLOOKUP(. Select a cell with a lookup value and type a comma.
    • Now, go to another Workbook and click on the Sheet. Select the range with the lookup value.
    • Enter a comma and select the cell ranges for the return array.
    • Type in all the arguments separated by a comma. Close the bracket and hit Enter. 

    Example 9: Two-Way Lookup

    So far, we learned a single lookup in all examples. Now, let’s learn the two-way lookup. For this, we will be nesting the XLOOKUP inside the XLOOKUP function. It is best when you need to return a value from the intersection of a Row or Column. 

    Formula: XLOOKUP(lookup_value1, lookup_array1, XLOOKUP(lookup_value2, lookup_array2, data_values))

    Example: Here, I have Education Expenses Data for different months. Suppose, I want to return the Amount of Miscellaneous for the month of February. 

    =XLOOKUP(I2, A3:A5, XLOOKUP(I3, B2:F2, B3:F5))
    • XLOOKUP(I3, B2:F2, B3:F5): Firstly, this formula returns the Row of return array which is 600, 90, 30.
    • XLOOKUP(I2, A3:A5, XLOOKUP(I3, B2:F2, B3:F5)): Then, the formula returns an item that matches the lookup value. As a result, we received 90.

    Make XLOOKUP Function More Dynamic

    While using the XLOOKUP function, do you change the lookup value in the formula every time? If you are, stop spending all of your time editing the arguments and let the formula recalculate itself.

    There’s an efficient way to make your Function more dynamic. We will create a drop-down list for our lookup value.

    1. Select the cell that is your lookup_value.
    2. From the Data Tab, click on Data Validation.
      From Data Tab and click on Data Validation
    3. On Data Validation dialogue box, be on the Settings tab. For Allow, click the drop-down list and pick List.
      Below Allow, choose List
    4. On Source, click the Collapse icon and select the Lookup array. Click OK.
      click the Collapse icon and select the Lookup array
    5. Now, if I pick another Sales Value, it’ll return a different employee and title automatically.
      Make XLOOKUP Dynamic with Data Validation

    Avoid Errors when using the XLOOKUP Function

    When you use the XLOOKUP function, these are the common errors you may get. In most cases, it occurs when the arguments of the formula haven’t been referenced properly. But, if you know the cause, it isn’t a big deal to dodge those errors.

    ErrorProbable Causes
    #N/A ErrorNo Lookup Value.
    No Exact Match.
    Data not sorted as required while using the Binary Search.
    #REF!Deleted Column that was Return Array.
    Source Workbook closed when using the formula for separate workbooks.
    #VALUE!When the lookup array is vertical and the return array is horizontal or vice versa. 
    #NAME? Text arguments without the “”
    Advanced Excel Excel Functions
    Nisha Gurung

      As a Business Student, Nisha first used Excel to outline data trends for her college projects. Later, she started exploring basic Excel functions when she realized it is a powerful tool for all businesses today. Nisha believes she can relate to many of the issues users face when starting their Excel Journey. She uses this knowledge and experience to curate contents that are fit for all types of Excel users. Apart from writing, she enjoys reading books and traveling.

      Related Posts

      Excel By Nisha Gurung

      How to Compare Two Columns in Excel Using VLOOKUP

      Excel By Nisha Gurung

      How to Share Excel Files with Multiple Users

      Excel By Nisha Gurung

      How to Filter Based on a List in Excel

      Add A Comment
      Table of ContentsToggle Table of ContentToggle
      • Benefits of Using XLOOKUP Functions
      • Tips to Use the XLOOKUP Function
      • Arguments Used in the XLOOKUP Function
      • Applications of XLOOKUP Function
        • Example 1: Vertical and Horizontal Lookup
        • Example 2: Exact Match
        • Example 3: Approximate Match
        • Example 4: Partial Match
        • Example 5: Different Search Modes
        • Example 6: Return Array
        • Example 7: XLOOKUP Across Sheets
        • Example 8: XLOOKUP with Different Workbooks
        • Example 9: Two-Way Lookup
      • Make XLOOKUP Function More Dynamic
      • Avoid Errors when using the XLOOKUP Function
      • Home
      • About Us
      • Privacy Policy
      © 2025 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.