How to use XLOOKUP Function/Formula in Excel – 16 Examples

Microsoft Excel offers a lookup and reference function called XLOOKUP. You may employ it to find a value in a range or an array and have a different range or array return the corresponding value.

Syntax of XLOOKUP Function in Excel

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_foud, match_mode, search_mode)

Argument of XLOOKUP Function in Excel

lookup_value: The value you wish to search for is the lookup value.

lookup_array: The area or array in which you want to look for a value.

return_array: The corresponding array or range from which you want to return a value is called the return array.

If_not_foud: Defines customized message if any data is not matched in lookup array

match_mode (optional): determines how exact or approximate the match should be.

The choices are,

• 0 or omitted, for an exact match,
• -1 for the next-smallest match, 
• 1 for the next-largest match,
• 2 for the next-largest match (wildcard match).

search_mode (optional): Determines whether the lookup array should be sorted in ascending or descending order 

• 1 (default): XLOOKUP function reads from first to last in the lookup array according to this option. 
• -1: XLOOKUP function reads from last to first in the lookup array order according to this option. 
• 2: To run a binary search in the data sorted in ascending order.
• -2: To run a binary search in the data sorted in descending order.

Return of XLOOKUP Function in Excel

The XLOOKUP function in Excel returns a result that matches a lookup value in a given array or range.

Type A. Behavior of XLOOKUP Function

Example #1: XLOOKUP Function to Find Exact Match

Suppose you have a data table containing product ID, name, and quantity. You want to return product Quantity by Product Name

Apply the following formula in the output cell G3

=XLOOKUP(F3,C2:C6,D2:D6)

Explanation:

• F3: This is the lookup value that you are looking for in the lookup array (C2:C6).
• C2:C6: The lookup array C2–C6 contains the items in which you want to look for the lookup value (F3).
• D2:D6: When the lookup value is found in the lookup array(C2:C6), the values you want to return are contained in the D2–D6 array, which is the return array. 
• All other parameters are omitted in the syntax of the XLOOKUP function, So XLOOKUP will take  match_mode as Zero(Exatch match) and search_mode as 1( first to last)

Example #2: XLOOKUP Function to Find Approximate Match

To find an approximate match, XLOOKUP provides two options:

•  To find the next-smallest match, enter the match mode as -1

=XLOOKUP(F4,D3:D7,C3:C7,,-1)

Explanation:

The search mode, “-1” in this case, instructs XLOOKUP to look for an exact match or, if one cannot be located, the next smaller item in the lookup array. We entered Quantity 13, So the XLOOKUP found the next smallest Quantity 12 and returned the corresponding Name from the range C3:C7

•  To find the  next-largest match, enter the match mode as 1

=XLOOKUP(F7,D3:D7,C3:C7,,1)

Explanation:

We entered Quantity 13, So the XLOOKUP found the next largest Quantity 15 and returned the corresponding Name from the range C3:C7

Example #3: XLOOKUP Function Can Perform both Left and Right  Lookup 

Unlike VLOOKUP, XLOOKUP offers more extensive features. The return_array can be located in the left or right position of lookup_array.

Example #4: XLOOKUP Function Can Handle Errors 

If the lookup_value is not found in the lookup_array, the XLOOKUP function returns an #N/A error. XLOOKUP has a built-in feature to show user-defined text instead of showing errors.

=XLOOKUP(F4,C3:C7,D3:D7,"Not found")

Explanation:

• In the range of cells C3–C7, the XLOOKUP formula will look up the value in F4. 
• If a match exists, the XLOOKUP formula will return the corresponding value from the cells D3–D7 range.
• The XLOOKUP formula will return the result “Not found” if the lookup value cannot be found in the lookup array.

Example #5: XLOOKUP Can Return Array or Multiple Values from Multiple Column

An array may be provided as a return by XLOOKUP. 

See the following example: Using ID as the lookup_value, name, and quantity being returned.

=XLOOKUP(F4,B3:B7,C3:D7)

Explanation:

• The cells B3–B7 range will be searched using the XLOOKUP formula for the value in F4.
• If a match is found, it will return the corresponding values(eg. Name and Quantity) from the cells in the C3–D7 range. This indicates that the formula will return any additional details contained in the return array in addition to the result associated with the lookup value.

Example #6: XLOOKUP Can Perform Horizontal Lookup

Suppose Product ID and Status are recorded Horizontally

You want to show product status in Table_A based on the Product ID

=XLOOKUP(B3:B7,G3:I3,G4:I4)

Explanation:

• In this Formula, We inserted three arrays as lookup value, lookup array, and return array. 
• B3:B7: The values that you wish to search for in the lookup array(G3:I3).
• G3:I3: This is the lookup range, which horizontally contains the values that you want to search for in the lookup array (B3:B7).
• G4:I4: After the lookup values are found in the lookup array, the values you want to return, are contained in the horizontal range G4:I4.

Example #7: XLOOKUP Returns the First Matched Value

Like VLOOKUP, XLOOKUP always returns the first match, whether it searches from 1st to last or last to first.

Search_Mode 1: Finds the first match in the first-to-last order.

=XLOOKUP(G3,C2:C6,D2:D6,,,1)

Search_mode -1: Finds the first match in last-to-first order.

=XLOOKUP(G3,C2:C6,D2:D6,,,-1)

Example #8: Partial Matching (wildcards) by XLOOKUP 

XLOOKUP allows finding partial matches by inserting wildcard characters in the syntax of the XLOOKUP function.

Suppose, you want to find the product ID based on the partial name of the salesman. Apply the following formula

=XLOOKUP("*"&G2&"*", D2:D6, B2:B6, ,2)

Explanation:

• “”&G2&””: The lookup value is represented by the text string “”&G2&”” and contains the value of cell G2 enclosed in asterisks. To enable for matches with text strings that contain the value in G2, the asterisks serve as wildcards.
• D2:D6: This is the lookup array
• B2:B6 : The values you want to return when a match is found in the lookup array 
• ,2: This is an optional argument that specifies the match mode, indicates that a “wildcard” search should be carried out by the XLOOKUP function.

Example #9: XLOOKUP is Case-Insensitive

XLOOKUP doesn’t care about cases. See the following example: the lookup_value is case-sensitive, but XLOOKUP returns the same value according to the first match behavior.

=XLOOKUP(G3,C2:C6,D2:D6,,,1)

Type B.  Application of Nested XLOOKUP 

Example #10: Self-Nested XLOOKUP Formula to Combine Data at Different Tables

Suppose you are given two tables. Table_X contains the product id, name, and quantity. Table_Y contains product ID, quantity, and price.

You want to find product prices by product name.

=XLOOKUP(XLOOKUP(B10,C3:C7,B3:B7),F3:F7,H3:H7)

Explanation:

• XLOOKUP(B10, C3:C7, B3:B7) looks for a value that matches the value in cell B10 in the range C3:C7 and returns the corresponding value from the range B3:B7.
• XLOOKUP(…, F3:F7, H3:H7): This XLOOKUP function uses the result of the inner XLOOKUP as the lookup value. The formula then provides the relevant value from the range H3:H7 after looking for a value in the range F3:F7 that matches the lookup value.

Example #11: Self-Nested XLOOKUP Formula to Switch Lookup Column

Let’s say you want to develop a formula that offers many choices for the search criteria. (eg, search by product ID, name, or quantity).

=XLOOKUP(H3,XLOOKUP(H2,B1:D1,B2:D6),E2:E6)

Similarly, you can modify the formula to switch the return_array column.

Explanation:

• The expression XLOOKUP(H2,B1:D1,B2:D6) looks for a value in the range B1:D1 that matches the value in cell H2 and returns the equivalent value from the range B2:D6.
• XLOOKUP(H3, …, E2:E6): The value returned from the inner XLOOKUP is used as the lookup array for this XLOOKUP function. This part of the formula searches for a value in the cell H3 in the lookup array and returns the corresponding value from the range E2:E6

Example #12: XLOOKUP Function Nested with IFNA Statement

You can apply the following formula to find the match from two tables in the same sheet

=IFNA(XLOOKUP(C9,B3:B7,D3:D7),XLOOKUP(C9,F3:F7,H3:H7))

Explanation:

• XLOOKUP(C9,B3:B7,D3:D7): This part of the formula first attempts to look up the value in cell C9 in the range B3:B7, and if a match is found, returns the corresponding value from the range D3:D7. If no match is found #N/A error is returned
• IFNA(results, XLOOKUP(C9,F3:F7,H3:H7)): The IFNA function checks whether the result of the first XLOOKUP function is an error (i.e. #N/A), and if so, performs another XLOOKUP to search for the same value in a different range (F3:F7). If a match is found, the corresponding value from H3:H7 is returned.

You can apply the following formula to get the same results.

=XLOOKUP(C9,B3:B7,D3:D7,XLOOKUP(C9,F3:F7,H3:H7))

Type C. XLOOKUP with  Multiple Criteria

Example #13: XLOOKUP With Boolean Expression

Suppose you want to run a search based on two criteria. XLOOKUP supports boolean expressions to verify multiple criteria.

=XLOOKUP(1,(B2:B6=H2)*(C2:C6=H3),E2:E6)

Explanation:

• (B2:B6=H2)*(C2:C6=H3): Based on whether the values in the B2:B6 range match the value in H2 And the values in the C2:C6 range match the value in H3, this portion of the formula employs Boolean multiplication to build an array of TRUE and FALSE values. When both requirements are TRUE, the multiplication yields an array with 1 for each row and 0 otherwise.
• XLOOKUP(1, (Boolean array), E2:E6): This function searches the Boolean array created with the previous argument for the value 1 and returns the corresponding value from the E2:E6 range.

Example #14: XLOOKUP With Concentrated Expression

You can obtain the same output as the previous example by applying the following formula

=XLOOKUP(H2&H3,B2:B6&C2:C6,E2:E6)

Explanation:

• H2&H3: The lookup value is created by concatenating the values in H2 and H3
• B2:B6&C2:C6: The lookup array is created by concatenating the values in the B2:B6 and C2:C6 ranges.
• XLOOKUP(H2&H3, … , E2:E6): The formula searches for the concatenated value of H2 and H3 in the concatenation of the B2:B6 and C2:C6 ranges, and returns the corresponding value from the E2:E6 range

Type D. Lookup Sources for XLOOKUP

Example #15: XLOOKUP to Search from Two Different Sheets

Suppose you have different tables, Table_X and Table_Y, on different sheets.

Table_X and Table_Y contain the product ID, name, and quantity. 

You want to make a formula to run a search in Table_Y when the lookup value is not found in Table_X

Explanation:

• If the value in cell C9 is found in the range B3:B7, this portion of the formula yields the comparable value from the range D3:D7. However, the second XLOOKUP function’s output will be returned if the XLOOKUP function is unsuccessful in finding a match.
• XLOOKUP(C9,Sheet7!B3:B7,Sheet7!D3:D7): This formula part searches for the value in cell C9 in the range B3:B7 of a different sheet called “Sheet7” and returns the corresponding value from the range D3:D7 of “Sheet7” if it finds a match.

Example #16: XLOOKUP to Search from Two Different Workbooks

You can modify the formula above to find the match from different workbooks as below:

=XLOOKUP(C9,B3:B7,D3:D7,XLOOKUP(C9,[Workbook2.xlsx]Sheet7!B3:B7,[Workbook2.xlsx]Sheet7!D3:D7))

When the outer XLOOKUP fails to match, then the inner XLOOKUP formula runs a search in the range B3:B7, located in the sheet named Sheet7 in Workbook2.

Things to Keep in Mind about XLOOKUP

• #N/A error: When the lookup value cannot be found in the lookup array, an error code of #N/A is generated. Verify again that the lookup value is spelled correctly and that it is formatted correctly to resolve this issue. Also, confirm that the lookup array is complete with all required information and that no cells are empty or missing.
• #REF! error: When the range reference in the XLOOKUP formula is invalid, the #REF! error appears. Verify the range references are accurate and free of errors or overlaps in order to correct this problem. Ensure the sheet name and cell reference are also included in the range references.
• #VALUE! error: This error happens when an invalid argument or an argument with the incorrect data type is used in an XLOOKUP formula. Check that the XLOOKUP formula’s arguments are correct and in the right order to resolve this issue. Furthermore, make sure there are no hidden characters in the cells that could lead to problems and that the data types are consistent.
• #NAME? error: Excel encounters this problem when it cannot recognize the XLOOKUP function. Make sure you are using an XLOOKUP-compatible version of Excel in order to correct this problem. Make sure the function name is spelled correctly and not preceded by extraneous letters or spaces.
• #NUM! error: This error happens when the value in the XLOOKUP formula is either too big or too small. Verify sure the values in the XLOOKUP formula are within the allowable range in order to correct this mistake.

XLOOKUP Function vs VLOOKUP Function in  Excel

Excel functions called XLOOKUP and VLOOKUP are both used to look up and retrieve data from tables. Nevertheless, XLOOKUP, a more recent and adaptable function, has a number of advantages over VLOOKUP, including:

• Whereas VLOOKUP only operates vertically, XLOOKUP may execute lookups in both directions. As a result, XLOOKUP is able to locate values both in the same row as the lookup value and in the same column.
• Like VLOOKUP, XLOOKUP can look up a value in an array of table columns and return a similar value from a different column in the same table. To get data from a completely separate range or array, XLOOKUP also lets you define a return array that is not in the same table as the lookup array.
• Comparing approximate matches is easier for XLOOKUP than VLOOKUP. Depending on the circumstance, XLOOKUP allows you to select whether to return the exact match, the next smaller match, or the next larger match. In the lookup value, wildcards can also be used for more versatile matching. Only exact matches can be handled by VLOOKUP.
• XLOOKUP’s syntax is less complex than VLOOKUP‘s, making it potentially simpler to use and comprehend. XLOOKUP can be made more efficient by having optional arguments that can be skipped if not required.

In conclusion, XLOOKUP is a more robust and adaptable function than VLOOKUP since it can handle both vertical and horizontal lookups, conduct approximations more precisely, and get data from a separate array. It is typically advised to use this function rather than VLOOKUP if you are using an Excel version that supports XLOOKUP.

XLOOKUP FUNCTION VS INDEX MATCH FUNCTION

The Excel functions XLOOKUP and INDEX-MATCH are both used to search for and retrieve data from tables. The following are some variations between the two:

• While INDEX-MATCH has been around for a while and is extensively used, XLOOKUP is a more recent function and might not be available in previous versions of Excel.
• Compared to INDEX-MATCH, XLOOKUP is simpler to use and has a simpler syntax. The INDEX-MATCH function takes numerous formulas and can be trickier to set up, but the XLOOKUP function has fewer arguments and is simpler to use.
• Whereas INDEX-MATCH needs a different algorithm for horizontal lookups, XLOOKUP can handle both vertical and horizontal lookups.
• XLOOKUP has more flexibility than INDEX-MATCH when doing approximations, enabling you to return the exact match, the next smaller match, or the next larger match, depending on the circumstance. Only exact matches can be handled by INDEX-MATCH.
• When you need to retrieve data from non-contiguous ranges, such as when you need to retrieve data from several columns or rows, INDEX-MATCH is more flexible than XLOOKUP.

In conclusion, XLOOKUP is a more streamlined and user-friendly function than INDEX-MATCH, capable of handling both vertical and horizontal lookups and performing approximate matches with more freedom. Contrarily, INDEX-MATCH is more flexible and can access data from non-contiguous ranges. The exact needs of your data and the version of Excel you are using will determine which of the two functions you should use.

Frequently Asked Questions 

Why XLOOKUP  is not working in Excel?

The Excel XLOOKUP function can not be functional for a number of reasons. The following list contains typical causes and remedies:

• Incorrect syntax:  Verify the XLOOKUP formula’s syntax to make sure it’s valid. Verify that all necessary arguments are present and are presented in the proper sequence. Make sure all parenthesis are correctly closed as well. To check and modify the formula, use the Formula Bar.
• Missing information: XLOOKUP throws a #N/A error if the lookup value is not present in the lookup array. Ensure that the lookup value is in the right format and has the proper spelling. Make sure the lookup array has all the necessary information and is free of empty or missing cells.
• Range errors: Make that the formula references the arrays correctly and that the ranges are not overlapping, moved, or shifted.
• Unsupported Excel version: XLOOKUP is a relatively new function, available in Office 365 and Microsoft Excel 2021. XLOOKUP might not function verify whether XLOOKUP is supported by your version of Excel and decide whether to use VLOOKUP or INDEX-MATCH instead.

If none of these fixes work, consider isolating the issue by disassembling the XLOOKUP formula into smaller components. Also, you can ask our Excel professionals for advice.