Excel XLOOKUP: Step-by-Step Examples to Use the XLOOKUP Function in Worksheet Formulas

In this Excel XLOOKUP Tutorial, you learn how to use the XLOOKUP function in worksheet formulas.

This Excel XLOOKUP Tutorial is currently under development. Subscribe to the Power Spreadsheets Newsletter and get future updates to this Excel XLOOKUP Tutorial.

This Excel XLOOKUP Tutorial is accompanied by an Excel workbook with the data and formulas I use in the examples below. Get this example workbook (for free) by clicking the button below.

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

• Excel VLOOKUP Tutorial (under development): Click here to open.
• Excel VLOOKUP from Another Sheet: Click here to open.
• Excel VLOOKUP Compare 2 Columns and Find Matches: Click here to open.
• Excel VLookup Sum Multiple Row Values (in Same Column): Click here to open.
• Excel VLOOKUP Multiple Columns: Click here to open.
• Excel VLOOKUP Sum Multiple Columns (Values): Click here to open.
• Excel VLookup Sum Multiple Column Values (with XLOOKUP): Click here to open.
• Excel VLookup Sum Multiple Rows and Columns: Click here to open.
• Excel VLookup Multiple Criteria with INDEX MATCH: Click here to open.
• Excel VLookup Multiple Criteria with XLOOKUP: Click here to open.
• Excel VLookup Multiple Criteria with the FILTER Function: Click here to open.
• Excel VLOOKUP Return Multiple Values with Helper Column: Click here to open.
• Excel VLookup Return Multiple Values with the INDEX Function: Click here to open.
• Excel VLookup Return Multiple Values with the FILTER Function: Click here to open.
• Excel VLookup Return Multiple Values in One Cell Separated by a Comma: Click here to open.
• Excel VLOOKUP Multiple Sheets: Click here to open.
• Excel VLOOKUP Multiple Sheets in Different Workbook: Click here to open.
• Excel VLOOKUP Sheet in Multiple Different Workbooks: Click here to open.

This Excel XLOOKUP Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

• Excel XLOOKUP Tutorial: Click here to open.
• Excel XLOOKUP in Table: Click here to open.
• Excel Nested XLOOKUP (Dynamic Lookup Value): Click here to open.
• Excel XLOOKUP If Not Found Return Blank: Click here to open.
• Excel IF XLOOKUP (for Error Handling): Click here to open.
• Excel XLOOKUP If Blank Return Blank: Click here to open.
• Excel XLOOKUP Wildcard: Click here to open.
• Excel XLOOKUP Between 2 Values: Click here to open.
• Excel XLookup Return All Matches: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

• Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
• Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
• Excel Macro Tutorial for Beginners: Click here to open.

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

• Premium Courses at the Power Spreadsheets Academy: Click here to open.
• Books at the Power Spreadsheets Library: Click here to open.

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.

(1) Excel XLOOKUP Exact Match

In this Section, you learn how to create an XLOOKUP exact match formula, where the XLOOKUP function searches for an exact match of the lookup value.

Excel XLOOKUP Exact Match Formula Template/Structure

The following is the Excel XLOOKUP exact match formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn)

Step-by-Step Process to Set Up an Excel XLOOKUP Exact Match Formula

Do the following to create an Excel XLOOKUP exact match formula:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Specify the first argument of the XLOOKUP function: lookup_value.

lookup_value is the value you search for (the lookup value) in the column (when doing a VLookup) or row (when doing an HLookup) you search in (and specify in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,

(3) Specify the second argument of the XLOOKUP function: lookup_array.

lookup_array is the column (when doing a VLookup) or row (when doing an HLookup) where you search for the lookup value (you specified in step #2).

As a general rule: When creating an XLOOKUP exact match formula, specify lookup_array as:

• A single-column (1 column, several rows) cell range, when doing a VLookup; or
• A single-row (1 row, several columns) cell range, when doing an HLookup.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,

(4) Specify the third argument of the XLOOKUP function: return_array.

return_array is the column (when doing a VLookup) or row (when doing an HLookup) with the value to return.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the cell range you specify as return_array is the same as that of the cell range you specified as lookup_array (in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn)

Additional Cues for Excel XLOOKUP Exact Match

(1) The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

(2) By default, XLOOKUP:

• Searches for an exact match of the lookup value.
• Works with the first entry matching the lookup value.
• Returns the #N/A error if it doesn't find an exact match of the lookup value.

Excel XLOOKUP Exact Match Example Worksheet

This Excel XLOOKUP Tutorial is accompanied by an Excel workbook with the data and formulas I use in the examples inside this Tutorial (including this XLOOKUP exact match example). Get this example workbook (for free) by clicking the button below.

The example worksheet (Exact Match) has 3 tables/sections with the following characteristics:

(1) Table 1 (cells A6 to H26).

The table:

• With the data.
• Where I search with the XLOOKUP exact match example formula.

Main characteristics:

• 8 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price, Total Sales).
• 1 header row (row 6).
• 20 entries (rows 7 to 26).

(2) Lookup Value (cells J6 and K6).

The lookup value (Salesperson 10) is stored in cell K6.

(3) Table 2 (cells J8 to L11).

The table where I:

• Set up the following example formulas:
  • The XLOOKUP exact match example formula; and
  • 2 equivalent example formulas, working with different function(s):
    • VLOOKUP.
    • INDEX MATCH.
• Display the results.

This table has 3 columns:

• Function(s): Column J (cells J8 to J11) lists the function(s) used by the example formulas I enter in column K.
• Total Sales: Column K (cells K8 to K11):
  • Stores the example formulas:
    • Cell K9 stores the XLOOKUP exact match example formula.
    • Cell K10 stores an equivalent VLOOKUP exact match example formula.
    • Cell K11 stores an equivalent INDEX MATCH exact match example formula.
  • Displays the results.
• Formula: Column L (cells L8 to L11) displays the example formulas I enter in column K.

I use the VLOOKUP exact match and INDEX MATCH exact match example formulas:

• For illustrative purposes.
• To compare XLOOKUP with 2 commonly used alternatives:
  • VLOOKUP (XLOOKUP vs. VLOOKUP).
  • INDEX MATCH (XLOOKUP vs. INDEX MATCH).

Excel XLOOKUP Exact Match Example Formula

I create the XLOOKUP exact match example formula as follows:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Specify the first argument of the XLOOKUP function: The lookup value.

The lookup value is stored in cell K6.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(K6,

(3) Specify the second argument of the XLOOKUP function: The column (I do a VLookup) where I search for the lookup value.

I search for the lookup value in column A:

• Cells A7 to A26; or
• A7:A26.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(K6,A7:A26,

(4) Specify the third argument of the XLOOKUP function: The column (I do a VLookup) with the value to return.

The column with the value to return is column H:

• Cells H7 to H26; or
• H7:H26.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(K6,A7:A26,H7:H26)

Excel XLOOKUP vs. VLOOKUP Exact Match Example Formulas

The following 2 example formulas are equivalents.

• I explain the XLOOKUP exact match example formula above.
• The VLOOKUP exact match example formula is an equivalent example formula working with the VLOOKUP function (vs. the XLOOKUP function).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP exact match example formula
=XLOOKUP(K6,A7:A26,H7:H26)

'VLOOKUP exact match example formula
=VLOOKUP(K6,A7:H26,8,FALSE)

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Lookup value.

In both cases, I use the first argument of the applicable function (lookup_value) to specify the lookup value (the value stored in cell K6): K6.

(2) Column where I search.

In the XLOOKUP exact match example formula, I specify the column where I search:

• With the second argument (lookup_array) of the XLOOKUP function.
• As a single-column (1 column, several rows) cell range: A7:A26.

In the VLOOKUP exact match example formula, I specify the table (cell range) where I look in:

• With the second argument (table_array) of the VLOOKUP function.
• As a table (cell range): A7:H26.

The VLOOKUP function searches for the lookup value inside the first/leftmost column of this table:

• Column A.
• Cells A7 to A26.

(3) Column with value to return.

In the XLOOKUP exact match example formula, I specify the column with the value to return:

• With the third argument (return_array) of the XLOOKUP function.
• As a single-column (1 column, several rows) cell range: H7:H26.

In the VLOOKUP exact match example formula, I specify the following:

• The table (cell range) where I look in (A7:H26) with the second argument (table_array) of the VLOOKUP function. This table contains the column with the value to return.
• The number of the column (inside the table where I look in) with the value to return using the third argument (col_index_num) of the VLOOKUP function: 8.

(4) Exact match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. I don't explicitly specify the match mode (exact vs. approximate) in the XLOOKUP exact match example formula.

The VLOOKUP function searches (by default) for an approximate match of the lookup value (also known as a range lookup). To make the VLOOKUP function (in the VLOOKUP exact match example formula) search for an exact match, I set the fourth argument of the VLOOKUP function (range_lookup) to FALSE.

Excel XLOOKUP vs. INDEX MATCH Exact Match Example Formulas

The following 2 example formulas are equivalents.

• I explain the XLOOKUP exact match example formula above.
• The INDEX MATCH exact match example formula is an equivalent example formula working with the INDEX and MATCH functions (vs. the XLOOKUP function).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP exact match example formula
=XLOOKUP(K6,A7:A26,H7:H26)

'INDEX MATCH exact match example formula
=INDEX(H7:H26,MATCH(K6,A7:A26,0))

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Formula structure.

I use a single function (XLOOKUP) to create the XLOOKUP exact match example formula.

I use 2 functions (INDEX and MATCH) to create the INDEX MATCH exact match example formula.

(2) Lookup value.

In both cases, I use the first argument of the applicable function (lookup_value) to specify the lookup value (the value stored in cell K6): K6.

• In the XLOOKUP exact match example formula, lookup_value is the first argument of the XLOOKUP function.
• In the INDEX MATCH exact match example formula, lookup_value is the first argument of the MATCH function.

(3) Column where I search.

In both cases, I use the second argument of the applicable function (lookup_array) to specify the column where I search as a single-column (1 column, several rows) cell range: A7:A26.

• In the XLOOKUP exact match example formula, lookup_array is the second argument of the XLOOKUP function.
• In the INDEX MATCH exact match example formula, lookup_array is the second argument of the MATCH function.

(4) Column with value to return.

In both cases, I use a single function argument to specify the column with the value to return as a single-column (1 column, several rows) cell range: H7:H26.

• In the XLOOKUP exact match example formula, I use the third argument (return_array) of the XLOOKUP function.
• In the INDEX MATCH exact match example formula, I use the first argument (array) of the INDEX function.

(5) Exact match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. I don't explicitly specify the match mode (exact vs. approximate) in the XLOOKUP exact match example formula.

The MATCH function searches (by default) for the largest value that is less than or equal to the lookup value. To make the MATCH function (in the INDEX MATCH exact match example formula) search for an exact match, I set the third argument of the MATCH function (match_type) to 0.

(2) Excel XLOOKUP Approximate Match

In this Section, you learn how to create an XLOOKUP approximate match formula to carry out a range XLOOKUP where you look up a value inside a range of values.

Excel XLOOKUP Approximate Match Formula Template/Structure

The following is the Excel XLOOKUP approximate match formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,,MatchMode)

Step-by-Step Process to Set Up an Excel XLOOKUP Approximate Match Formula

Do the following to create an Excel XLOOKUP approximate match formula:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Specify the first argument of the XLOOKUP function: lookup_value.

lookup_value is the value you search for (the lookup value) in the column (when doing a VLookup) or row (when doing an HLookup) you search in (and specify in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,

(3) Specify the second argument of the XLOOKUP function: lookup_array.

lookup_array is the column (when doing a VLookup) or row (when doing an HLookup) where you search for the lookup value (you specified in step #2).

As a general rule: When creating an XLOOKUP approximate match formula, specify lookup_array as:

• A single-column (1 column, several rows) cell range, when doing a VLookup; or
• A single-row (1 row, several columns) cell range, when doing an HLookup.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,

(4) Specify the third argument of the XLOOKUP function: return_array.

return_array is the column (when doing a VLookup) or row (when doing an HLookup) with the value to return.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the cell range you specify as return_array is the same as that of the cell range you specified as lookup_array (in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,

(5) Omit the fourth argument of the XLOOKUP function: if_not_found.

if_not_found is:

• The text or value returned by the XLOOKUP function if it fails to find a valid match for the lookup value (you specified in step #2).
• Optional. If you omit the if_not_found argument (as in this XLOOKUP approximate match formula template/structure) and the XLOOKUP function fails to find a valid match for the lookup value (you specified in step #2), XLOOKUP returns the #N/A error.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,,

(6) Specify the fifth argument of the XLOOKUP function: match_mode.

match_mode specifies how (exact vs. approximate) the XLOOKUP function matches the lookup value (you specified in step #2) against the values in the lookup array (you specified in step #3).

When creating an XLOOKUP approximate match formula, set match_mode to one of the following values:

• -1: Exact match or (if no exact match is found) return the next smaller item. Consider setting match_mode to -1 if the lookup array (you specified in step #3) lists the lower bounds (of the applicable ranges).
• 1: Exact match or (if no exact match is found) return the next larger item. Consider setting match_mode to 1 if the lookup array (you specified in step #3) lists the upper bounds (of the applicable ranges).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,,MatchMode)

Additional Cues for Excel XLOOKUP Approximate Match

(1) The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

(2) By default, XLOOKUP:

• Works with the first entry matching the lookup value.
• Returns the #N/A error if it fails to find a valid match for the lookup value.

Excel XLOOKUP Approximate Match Example Worksheet

This Excel XLOOKUP Tutorial is accompanied by an Excel workbook with the data and formulas I use in the examples inside this Tutorial (including this XLOOKUP approximate match example). Get this example workbook (for free) by clicking the button below.

The example worksheet (Approximate Match) has 2 tables/sections with the following characteristics:

(1) Table 1 (cells A6 to J26).

The table where I:

• Set up the following example formulas:
  • The XLOOKUP approximate match example formulas; and
  • 2 equivalent example formulas, working with different function(s):
    • VLOOKUP.
    • INDEX MATCH.
• Display the results.

This Table has 10 columns:

• Salesperson, Units, Unit Price, and Total Sales have example data.
• Commission with XLOOKUP, Commission with VLOOKUP, and Commission with INDEX MATCH: Columns E (cells E6 to E26), G (cells G6 to G26), and I (cells I6 to I26):
  • Store the example formulas:
    • Column E stores the XLOOKUP approximate match example formulas.
    • Column G stores equivalent VLOOKUP approximate match example formulas.
    • Column I stores equivalent INDEX MATCH approximate match example formulas.
  • Display the results.
• XLOOKUP Approximate Match Formula, VLOOKUP Approximate Match Formula, and INDEX MATCH Approximate Match Formula: Columns F (cells F6 to F26), H (cells H6 to H26), and J (cells J6 to J26) display the example formulas:
  • Column F displays the XLOOKUP approximate match example formulas I enter in column E.
  • Column H displays the VLOOKUP approximate match example formulas I enter in column G.
  • Column J displays the INDEX MATCH approximate match example formulas I enter in column I.

I use the VLOOKUP approximate match and INDEX MATCH approximate match example formulas:

• For illustrative purposes.
• To compare XLOOKUP with 2 commonly used alternatives:
  • VLOOKUP (XLOOKUP vs. VLOOKUP).
  • INDEX MATCH (XLOOKUP vs. INDEX MATCH).

(2) Table 2 (cells L6 to M14).

The table:

• With the data.
• Where I search with the XLOOKUP approximate match example formula.

Main characteristics:

• 2 columns (Total Sales are Greater Than or Equal to, Commission).
• 1 header row (row 6).
• 8 entries (rows 7 to 14).

Excel XLOOKUP Approximate Match Example Formula

I create the XLOOKUP approximate match example formula for row 7 (cell E7 of the example worksheet) as follows:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Specify the first argument of the XLOOKUP function: The lookup value.

The lookup value is stored in column D (cell D7).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(D7,

(3) Specify the second argument of the XLOOKUP function: The column (I do a VLookup) where I search for the lookup value.

I search for the lookup value in column L:

• Cells L7 to L14; or
• $L$7:$L$14 (using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(D7,$L$7:$L$14,

(4) Specify the third argument of the XLOOKUP function: The column (I do a VLookup) with the value to return.

The column with the value to return is column M:

• Cells M7 to M14; or
• $M$7:$M$14 (using absolute references).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(D7,$L$7:$L$14,$M$7:$M$14,

(5) Omit the fourth argument of the XLOOKUP function: if_not_found.

If the XLOOKUP function fails to find a valid match for the lookup value (I specified in step #2), the XLOOKUP approximate match example formula returns the #N/A error.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(D7,$L$7:$L$14,$M$7:$M$14,,

(6) Specify the fifth argument of the XLOOKUP function: The match type (match_mode) used by XLOOKUP to match the lookup value (I specified in step #2) against the values in the lookup array (I specified in step #3).

I set match_mode to -1 (exact match or (if no exact match is found) return the next smaller item).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(D7,$L$7:$L$14,$M$7:$M$14,,-1)

(7) Copy the XLOOKUP approximate match example formula (I entered in cell E7), and paste it in rows 8 to 26 (cells E8 to E26).

Excel XLOOKUP vs. VLOOKUP Approximate Match Example Formulas

The following 2 example formulas are equivalents.

• I explain the XLOOKUP approximate match example formula above.
• The VLOOKUP approximate match example formula is an equivalent example formula working with the VLOOKUP function (vs. the XLOOKUP function).

These example formulas are stored in row 7 of the example worksheet (cells E7 and G7).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP approximate match example formula
=XLOOKUP(D7,$L$7:$L$14,$M$7:$M$14,,-1)

'VLOOKUP approximate match example formula
=VLOOKUP(D7,$L$7:$M$14,2,TRUE)

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Lookup value.

In both cases, I use the first argument of the applicable function (lookup_value) to specify the lookup value (the value stored in column D): D7.

(2) Column where I search.

In the XLOOKUP approximate match example formula, I specify the column where I search:

• With the second argument (lookup_array) of the XLOOKUP function.
• As a single-column (1 column, several rows) cell range: $L$7:$L$14 (using absolute references).

In the VLOOKUP approximate match example formula, I specify the table (cell range) where I look in:

• With the second argument (table_array) of the VLOOKUP function.
• As a table (cell range): $L$7:$M$14 (using absolute references).

The VLOOKUP function searches for the lookup value inside the first/leftmost column of this table:

• Column L.
• Cells L7 to L14.

(3) Column with value to return.

In the XLOOKUP approximate match example formula, I specify the column with the value to return:

• With the third argument (return_array) of the XLOOKUP function.
• As a single-column (1 column, several rows) cell range: $M$7:$M$14 (using absolute references).

In the VLOOKUP approximate match example formula, I specify the following:

• The table (cell range) where I look in ($L$7:$M$14 using absolute references) with the second argument (table_array) of the VLOOKUP function. This table contains the column with the value to return.
• The number of the column (inside the table where I look in) with the value to return using the third argument (col_index_num) of the VLOOKUP function: 2.

(4) Value returned if lookup value isn't found.

By default, the XLOOKUP function returns the #N/A error if it fails to find a valid match for the lookup value. I can (theoretically) work with the fourth argument of the XLOOKUP function (if_not_found) to (explicitly) specify the text or value returned by XLOOKUP if it fails to find a valid match for the lookup value.

In the XLOOKUP approximate match example formula, I omit the fourth argument of the XLOOKUP function.

The VLOOKUP function returns the #N/A error if it fails to find a valid match for the lookup value.

(5) Approximate match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. In the XLOOKUP approximate match example formula, I specify the match mode (exact vs. approximate) with the fifth argument (match_mode) of the XLOOKUP function. I set match_mode to -1 (exact match or (if no exact match is found) return the next smaller item).

The VLOOKUP function searches (by default) for an approximate match of the lookup value (the largest value that is less than or equal to the lookup value). To make this approximate match mode explicit, I set the fourth argument of the VLOOKUP function (range_lookup) to TRUE.

(6) Sorting of column where I search.

The data in the column where I search (column L in the example worksheet) is sorted in ascending order. This is required by the VLOOKUP function, but not by the XLOOKUP function.

In other words:

• The XLOOKUP function (when creating an XLOOKUP approximate match formula) does not require the data in the column where I search to be sorted in ascending order; but
• The VLOOKUP function (when creating a VLOOKUP approximate match formula) requires the data in the column where I search to be sorted in ascending order.

Excel XLOOKUP vs. INDEX MATCH Approximate Match Example Formulas

The following 2 example formulas are equivalents.

• I explain the XLOOKUP approximate match example formula above.
• The INDEX MATCH approximate match example formula is an equivalent example formula working with the INDEX and MATCH functions (vs. the XLOOKUP function).

These example formulas are stored in row 7 of the example worksheet (cells E7 and I7).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP approximate match example formula
=XLOOKUP(D7,$L$7:$L$14,$M$7:$M$14,,-1)

'INDEX MATCH approximate match example formula
=INDEX($M$7:$M$14,MATCH(D7,$L$7:$L$14,1))

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Formula structure.

I use a single function (XLOOKUP) to create the XLOOKUP approximate match example formula.

I use 2 functions (INDEX and MATCH) to create the INDEX MATCH approximate match example formula.

(2) Lookup value.

In both cases, I use the first argument of the applicable function (lookup_value) to specify the lookup value (the value stored in cell K6): D7.

• In the XLOOKUP approximate match example formula, lookup_value is the first argument of the XLOOKUP function.
• In the INDEX MATCH approximate match example formula, lookup_value is the first argument of the MATCH function.

(3) Column where I search.

In both cases, I use the second argument of the applicable function (lookup_array) to specify the column where I search as a single-column (1 column, several rows) cell range: $L$7:$L$14 (using absolute references).

• In the XLOOKUP approximate match example formula, lookup_array is the second argument of the XLOOKUP function.
• In the INDEX MATCH approximate match example formula, lookup_array is the second argument of the MATCH function.

(4) Column with value to return.

In both cases, I use a single function argument to specify the column with the value to return as a single-column (1 column, several rows) cell range: H7:H26.

• In the XLOOKUP approximate match example formula, I use the third argument (return_array) of the XLOOKUP function.
• In the INDEX MATCH approximate match example formula, I use the first argument (array) of the INDEX function.

(5) Approximate match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. In the XLOOKUP approximate match example formula, I specify the match mode (exact vs. approximate) with the fifth argument (match_mode) of the XLOOKUP function. I set match_mode to -1 (exact match or (if no exact match is found) return the next smaller item).

The MATCH function searches (by default) for the largest value that is less than or equal to the lookup value. To make this approximate match mode explicit, I set the third argument of the MATCH function (match_type) to 1.

(6) Sorting of column where I search.

The data in the column where I search (column L in the example worksheet) is sorted in ascending order. This is required by the MATCH function, but not by the XLOOKUP function.

In other words:

• The XLOOKUP function (when creating an XLOOKUP approximate match formula) does not require the data in the column where I search to be sorted in ascending order; but
• The MATCH function (when creating an INDEX MATCH approximate match formula) requires the data in the column where I search to be sorted in ascending order.

(3) Excel XLOOKUP Return Multiple Values (Columns or Rows)

In this Section, you learn how to create an XLOOKUP return multiple values (columns or rows) formula, where the XLOOKUP function returns multiple values from:

• Multiple columns (when doing a VLookup); or
• Multiple rows (when doing an HLookup);

Associated to the lookup value.

Excel XLOOKUP Return Multiple Values (Columns or Rows) Formula Template/Structure

The following is the Excel XLOOKUP return multiple values (columns or rows) formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnsOrRowsWithMultipleValuesToReturn)

Step-by-Step Process to Set Up an Excel XLOOKUP Return Multiple Values (Columns or Rows) Formula

Do the following to create an Excel XLOOKUP return multiple values (columns or rows) formula:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Specify the first argument of the XLOOKUP function: lookup_value.

lookup_value is the value you search for (the lookup value) in the column (when doing a VLookup) or row (when doing an HLookup) you search in (and specify in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,

(3) Specify the second argument of the XLOOKUP function: lookup_array.

lookup_array is the column (when doing a VLookup) or row (when doing an HLookup) where you search for the lookup value (you specified in step #2).

As a general rule: When creating an XLOOKUP return multiple values (columns or rows) formula, specify lookup_array as:

• A single-column (1 column, several rows) cell range, when doing a VLookup; or
• A single-row (1 row, several columns) cell range, when doing an HLookup.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,

(4) Specify the third argument of the XLOOKUP function: return_array.

return_array is the columns (when doing a VLookup) or rows (when doing an HLookup) with the multiple values to return. The number of columns (when doing a VLookup) or rows (when doing an HLookup) of the cell range you specify as return_array should be equal to the number of multiple values to return.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the cell range you specify as return_array is the same as that of the cell range you specified as lookup_array (in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnsOrRowsWithMultipleValuesToReturn)

Additional Cues for Excel XLOOKUP Return Multiple Values (Columns or Rows)

(1) The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

(2) By default, XLOOKUP:

• Searches for an exact match of the lookup value.
• Works with the first entry matching the lookup value.
• Returns the #N/A error if it fails to find a valid match for the lookup value.

Excel XLOOKUP Return Multiple Values (Columns or Rows) Example Worksheet

This Excel XLOOKUP Return Multiple Values (Columns or Rows) Tutorial is accompanied by an Excel workbook with the data and formulas I use in the examples inside this Tutorial (including this example). Get this example workbook (for free) by clicking the button below.

The example worksheet (Return Multiple Values C or R) has 3 tables/sections with the following characteristics:

(1) Table 1 (cells A6 to H26).

The table:

• With the data.
• Where I search with the XLOOKUP return multiple values (columns) example formula.

Main characteristics:

• 8 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price, Total Sales).
• 1 header row (row 6).
• 20 entries (rows 7 to 26).

(2) Lookup Value (cells J6 and K6).

The lookup value (Salesperson 10) is stored in cell K6.

(3) Table 2 (cells J8 to R11).

The table where I:

• Set up the following example formulas:
  • The XLOOKUP return multiple values (columns) example formula; and
  • 2 equivalent example formulas, working with different function(s):
    • VLOOKUP.
    • INDEX MATCH.
• Display the results.

This table has 9 columns:

• Function(s): Column J (cells J8 to K11) lists the function(s) used by the example formulas I enter in column K.
• Date, Customer, Product, City, Units, Unit Price, Total Sales: Columns K to Q (cells K8 to Q11):
  • Have the same headers as columns B to H (cells B6 to H6) in Table 1 (described above).
  • Store the example formulas:
    • Row 9 (cell K9) stores the XLOOKUP return multiple values (columns) example formula. The results from the example formula spill to cells L9 to Q9.
    • Row 10 (cell K10) stores the VLOOKUP return multiple values (columns) example formula. The results from the example formula spill to cells L10 to Q10.
    • Row 11 (cell K11) stores the INDEX MATCH return multiple values (columns) example formula. The results from the example formula spill to cells L11 to Q11.
• Formula: Column R (cells R8 to R11) displays the example formulas I enter in column K.

I use the VLOOKUP return multiple values (columns) and INDEX MATCH return multiple values (columns) example formulas:

• For illustrative purposes.
• To compare XLOOKUP with 2 commonly used alternatives:
  • VLOOKUP (XLOOKUP vs. VLOOKUP).
  • INDEX MATCH (XLOOKUP vs. INDEX MATCH).

Excel XLOOKUP Return Multiple Values (Columns) Example Formula

I create the XLOOKUP return multiple values (columns) example formula as follows:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Specify the first argument of the XLOOKUP function: The lookup value.

The lookup value is stored in cell K6.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(K6,

(3) Specify the second argument of the XLOOKUP function: The column (I do a VLookup) where I search for the lookup value.

I search for the lookup value in column A:

• Cells A7 to A26; or
• A7:A26.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(K6,A7:A26,

(4) Specify the third argument of the XLOOKUP function: The columns (I do a VLookup) with the multiple values to return.

The columns with the value to return are columns B to H:

• Cells B7 to H26; or
• B7:H26.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(K6,A7:A26,B7:H26)

Excel XLOOKUP vs. VLOOKUP Return Multiple Values (Columns)

The following 2 example formulas are equivalents.

• I explain the XLOOKUP return multiple values (columns) example formula above.
• The VLOOKUP return multiple values (columns) example formula is an equivalent example formula working with the VLOOKUP function (vs. the XLOOKUP function).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP return multiple values (columns) example formula
=XLOOKUP(K6,A7:A26,B7:H26)

'VLOOKUP return multiple values (columns) example formula
=VLOOKUP(K6,A7:H26,{2,3,4,5,6,7,8},FALSE)

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Lookup value.

In both cases, I use the first argument of the applicable function (lookup_value) to specify the lookup value (the value stored in cell K6): K6.

(2) Column where I search.

In the XLOOKUP return multiple values (columns) example formula, I specify the column where I search:

• With the second argument (lookup_array) of the XLOOKUP function.
• As a single-column (1 column, several rows) cell range: A7:A26.

In the VLOOKUP return multiple values (columns) example formula, I specify the table (cell range) where I look in:

• With the second argument (table_array) of the VLOOKUP function.
• As a table (cell range): A7:H26.

The VLOOKUP function searches for the lookup value inside the first/leftmost column of this table:

• Column A.
• Cells A7 to A26.

(3) Columns with multiple values to return.

In the XLOOKUP return multiple values (columns) example formula, I specify the columns with the multiple values to return:

• With the third argument (return_array) of the XLOOKUP function.
• As a 7-column (7 column, several rows) cell range: B7:H26.

In the VLOOKUP return multiple values (columns) example formula, I specify the following:

• The table (cell range) where I look in (A7:H26) with the second argument (table_array) of the VLOOKUP function. This table contains the columns with the multiple values to return.
• The number of the columns (inside the table where I look in) with the multiple values to return using the third argument (col_index_num) of the VLOOKUP function as a comma-delimited list wrapped in curly braces ({}): {2,3,4,5,6,7,8}.

(4) Exact match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. I don't explicitly specify the match mode (exact vs. approximate) in the XLOOKUP return multiple values (columns) example formula.

The VLOOKUP function searches (by default) for an approximate match of the lookup value (also known as a range lookup). To make the VLOOKUP function (in the VLOOKUP return multiple values (columns) example formula) search for an exact match, I set the fourth argument of the VLOOKUP function (range_lookup) to FALSE.

(5) Entering the formula as an array formula.

Both (XLOOKUP and VLOOKUP return multiple values (columns)) example formulas are array formulas.

• The XLOOKUP function is available in Excel 2021 and later (including Excel 365).
• If you're working with Excel 2019 or earlier, you must enter the VLOOKUP return multiple values (columns) example formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

Excel XLOOKUP vs. INDEX MATCH Return Multiple Values (Columns) Example Formulas

The following 2 example formulas are equivalents.

• I explain the XLOOKUP return multiple values (columns) example formula above.
• The INDEX MATCH return multiple values (columns) example formula is an equivalent example formula working with the INDEX and MATCH functions (vs. the XLOOKUP function).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP return multiple values (columns) example formula
=XLOOKUP(K6,A7:A26,B7:H26)

'INDEX MATCH return multiple values (columns) example formula
=INDEX(B7:H26,MATCH(K6,A7:A26,0),{1,2,3,4,5,6,7})

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Formula structure.

I use a single function (XLOOKUP) to create the XLOOKUP return multiple values (columns) example formula.

I use 2 functions (INDEX and MATCH) to create the INDEX MATCH return multiple values (columns) example formula.

(2) Lookup value.

In both cases, I use the first argument of the applicable function (lookup_value) to specify the lookup value (the value stored in cell K6): K6.

• In the XLOOKUP return multiple values (columns) example formula, lookup_value is the first argument of the XLOOKUP function.
• In the INDEX MATCH return multiple values (columns) example formula, lookup_value is the first argument of the MATCH function.

(3) Column where I search.

In both cases, I use the second argument of the applicable function (lookup_array) to specify the column where I search as a single-column (1 column, several rows) cell range: A7:A26.

• In the XLOOKUP return multiple values (columns) example formula, lookup_array is the second argument of the XLOOKUP function.
• In the INDEX MATCH return multiple values (columns) example formula, lookup_array is the second argument of the MATCH function.

(4) Columns with multiple values to return.

In the XLOOKUP return multiple values (columns) example formula, I specify the columns with the multiple values to return:

• With the third argument (return_array) of the XLOOKUP function.
• As a 7-column (7 column, several rows) cell range: B7:H26.

In the INDEX MATCH return multiple values (columns) example formula, I specify the following:

• The columns with the multiple values to return:
  • With the first argument (array) of the INDEX function.
  • As a 7-column (7 column, several rows) cell range: B7:H26.
• The number (inside this 7-column cell range) of the columns with the multiple values to return using the third argument (column_num) of the INDEX function as a comma-delimited list wrapped in curly braces ({}): {1,2,3,4,5,6,7}.

(5) Exact match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. I don't explicitly specify the match mode (exact vs. approximate) in the XLOOKUP return multiple values (columns) example formula.

The MATCH function searches (by default) for the largest value that is less than or equal to the lookup value. To make the MATCH function (in the INDEX MATCH return multiple values (columns) example formula) search for an exact match, I set the third argument of the MATCH function (match_type) to 0.

(6) Entering the formula as an array formula.

Both (XLOOKUP and VLOOKUP return multiple values (columns)) example formulas are array formulas.

• The XLOOKUP function is available in Excel 2021 and later (including Excel 365).
• If you're working with Excel 2019 or earlier, you must enter the VLOOKUP return multiple values (columns) example formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

(4) Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator)

In this Section, you learn how to create an XLOOKUP multiple criteria (with the ampersand text concatenation operator) formula, where the XLOOKUP function:

• Considers multiple criteria; and
• Relies on the ampersand (&) text concatenation operator to specify the multiple criteria.

Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator) Formula Template/Structure

The following is the Excel XLOOKUP multiple criteria (with the ampersand text concatenation operator) formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(Criterion1&Criterion2&...&Criterion#,ColumnOrRowWithCriterion1&ColumnOrRowWithCriterion2&...&ColumnOrRowWithCriterion#,ColumnsOrRowsWithValuesToReturn)

Step-by-Step Process to Set Up an Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator) Formula

Do the following to create an Excel XLOOKUP multiple criteria (with the ampersand text concatenation operator) formula:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Use the ampersand (&) text concatenation operator to:

• Concatenate the multiple criteria (Criterion1&Criterion2&…&Criterion#); and
• Specify the first argument of the XLOOKUP function (lookup_value).

lookup_value is the value you search for (the lookup value) in the array you search in (and create in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(Criterion1&Criterion2&...&Criterion#,

(3) Use the ampersand (&) text concatenation operator to:

• Concatenate the cell ranges with the multiple criteria (ColumnOrRowWithCriterion1&ColumnOrRowWithCriterion2&…&ColumnOrRowWithCriterion#); and
• Specify the second argument of the XLOOKUP function (lookup_array).

lookup_array is the array where you search for the lookup value (you specified in step #2).

As a general rule: When concatenating the cell ranges with the multiple criteria, specify these cell ranges as:

• Single-column (1 column, several rows) cell ranges, when doing a VLookup; or
• Single-row (1 row, several columns) cell ranges, when doing an HLookup.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(Criterion1&Criterion2&...&Criterion#,ColumnOrRowWithCriterion1&ColumnOrRowWithCriterion2&...&ColumnOrRowWithCriterion#,

(4) Specify the third argument of the XLOOKUP function: return_array.

return_array is the column(s) (when doing a VLookup) or row(s) (when doing an HLookup) with the value(s) to return. The number of columns (when doing a VLookup) or rows (when doing an HLookup) of the cell range you specify as return_array should be equal to the number of values to return.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the cell range you specify as return_array is the same as that of the cell ranges you worked with to specify lookup_array (in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(Criterion1&Criterion2&...&Criterion#,ColumnOrRowWithCriterion1&ColumnOrRowWithCriterion2&...&ColumnOrRowWithCriterion#,ColumnsOrRowsWithValuesToReturn)

Additional Cues for Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator)

(1) The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

(2) By default, XLOOKUP:

• Searches for an exact match of the lookup value.
• Works with the first entry matching the lookup value.
• Returns the #N/A error if it fails to find a valid match for the lookup value.

(3) Concatenate as many:

• Individual criteria (when specifying the lookup value); or
• Column(s) or row(s) with criteria (when specifying the lookup array);

As needed.

(4) The XLOOKUP multiple criteria (with the ampersand text concatenation operator) formula template/structure I explain above isn't the only formula template/structure you can use to create an XLOOKUP multiple criteria formula.

You can work with Boolean logic to specify XLOOKUP's first 2 arguments (lookup_value and lookup_array) when creating an XLOOKUP multiple criteria (with Boolean logic) formula.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(Criterion1[ComparisonOperator]ColumnOrRowWithCriterion1)*(Criterion2[ComparisonOperator]ColumnOrRowWithCriterion2)*…*(Criterion#[ComparisonOperator]ColumnOrRowWithCriterion#),ColumnsOrRowsWithValuesToReturn)

This XLOOKUP multiple criteria (with Boolean logic) formula template/structure is (as a general rule) more powerful and flexible (vs. the XLOOKUP multiple criteria (with the ampersand text concatenation operator) formula template/structure I explain above). Among others, the XLOOKUP multiple criteria (with Boolean logic) formula template/structure is easier to implement when:

• Carrying out more complex lookups; or
• Working with additional constructs, such as additional operators and worksheet functions.

Please refer to the Section on Excel XLOOKUP Multiple Criteria (with Boolean Logic) for a more detailed explanation on how to create an XLOOKUP multiple criteria (with Boolean logic) formula, where the XLOOKUP function:

• Considers multiple criteria; and
• Relies on Boolean logic to test whether the multiple criteria are met.

Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator) Example Worksheet

This Excel XLOOKUP Tutorial is accompanied by an Excel workbook with the data and formulas I use in the examples inside this Tutorial (including this example). Get this example workbook (for free) by clicking the button below.

The example worksheet (Multiple Criteria Concatenation) has 3 tables/sections with the following characteristics:

(1) Table 1 (cells A6 to I26).

The table:

• With the data.
• Where I search with the XLOOKUP multiple criteria (with Boolean logic) example formula.

Main characteristics:

• 9 columns (Salesperson, Date, VLOOKUP Helper Column, Customer, Product, City, Units, Unit Price, Total Sales).
• 1 header row (row 6).
• 20 entries (rows 7 to 26).

(2) Multiple Criteria (cells K6 to L7).

2 criteria (Salesperson and Date) are stored in cells:

• L6 (Salesperson).
• L7 (Date).

(3) Table 2 (cells K9 to M12).

The table where I:

• Set up the following example formulas:
  • The XLOOKUP multiple criteria (with Boolean logic) example formula; and
  • 2 equivalent example formulas, working with different function(s):
    • VLOOKUP.
    • INDEX MATCH.
• Display the results.

This table has 3 columns:

• Function(s): Column K (cells K9 to K12) lists the function(s) used by the example formulas I enter in column L.
• Total Sales: Column L (cells L9 to L12):
  • Stores the example formulas:
    • Cell L10 stores the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula.
    • Cell L11 stores an equivalent VLOOKUP multiple criteria example formula.
    • Cell L12 stores an equivalent INDEX MATCH multiple criteria example formula.
  • Displays the results.
• Formula: Column M (cells M9 to M12) displays the example formulas I enter in column L.

I use the VLOOKUP multiple criteria and INDEX MATCH multiple criteria example formulas:

• For illustrative purposes.
• To compare XLOOKUP with 2 commonly used alternatives:
  • VLOOKUP (XLOOKUP vs. VLOOKUP).
  • INDEX MATCH (XLOOKUP vs. INDEX MATCH).

Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator) Example Formula

I create the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula as follows:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Use the ampersand (&) text concatenation operator to:

• Concatenate the multiple criteria; and
• Specify the first argument of the XLOOKUP function (lookup_value).

The multiple criteria considered by the XLOOKUP multiple criteria (with the ampersand text concatenation operator) are stored in:

• Cell L6; and
• Cell L7.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(L6&L7,

(3) Use the ampersand (&) text concatenation operator to:

• Concatenate the cell ranges with the multiple criteria; and
• Specify the second argument of the XLOOKUP function (lookup_array).

The columns (I do a VLookup) with the multiple criteria considered by the XLOOKUP multiple criteria (with the ampersand text concatenation operator) are:

• Column A (cells A7 to A26; A7:A26); and
• Column B (cells B7 to B26; B7:B26).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(L6&L7,A7:A26&B7:B26,

(4) Specify the third argument of the XLOOKUP function: The column (I do a VLookup) with the value to return.

The column with the value to return is column I:

• Cells I7 to I26; or
• I7:I26.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(L6&L7,A7:A26&B7:B26,I7:I26)

Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator) vs. VLOOKUP Multiple Criteria Example Formulas

The following 2 example formulas are equivalents.

• I explain the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula above.
• The VLOOKUP multiple criteria example formula is an equivalent example formula working with the VLOOKUP function (vs. the XLOOKUP function).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula
=XLOOKUP(L6&L7,A7:A26&B7:B26,I7:I26)

'VLOOKUP multiple criteria example formula
=VLOOKUP(L6&L7,C7:I26,7,FALSE)

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Solution structure.

The XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula works with:

• The original (unmodified) data.
• 2 separate columns when testing whether the multiple conditions are met:
  • Column A (cells A7 to A26; A7:A26); and
  • Column B (cells B7 to B26; B7:B26).

The VLOOKUP multiple criteria example formula relies on a helper column (column C, cells C7 to C26). I use this helper column to concatenate (with the ampersand -&- text concatenation operator) the data in the 2 columns the VLOOKUP multiple criteria example formula considers when testing whether the multiple conditions are met:

• Column A (cells A7 to A26; A7:A26); and
• Column B (cells B7 to B26; B7:B26).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'Formula in cell C7
=A7&B7

(2) Lookup value.

In both cases I:

• Use the first argument of the applicable function (lookup_value) to specify the lookup value; and
• Set this first argument (lookup_value) to a text value concatenating the 2 criteria I consider:
  • Salesperson, stored in cell L6; and
  • Date, stored in cell L7.

I use the ampersand (&) text concatenation operator to create this concatenated text value: L6&L7.

(3) Column or array where I search.

In the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula, I:

• Use the second argument of the XLOOKUP function (lookup_array) to specify the array where I search; and
• Use the ampersand (&) text concatenation operator to create an array with the contents stored in the cell ranges with the 2 criteria I consider.
  • Column A (cells A7 to A26; A7:A26); and
  • Column B (cells B7 to B26; B7:B26).

In the VLOOKUP multiple criteria example formula, I specify the table (cell range) where I look in:

• With the second argument (table_array) of the VLOOKUP function.
• As a table (cell range): C7:I26.

The VLOOKUP function searches for the lookup value inside the first/leftmost column of this table:

• Column C.
• Cells C7 to C26.

This column C is a helper column. I use this helper column to concatenate the data in the 2 columns the VLOOKUP multiple criteria example formula considers when testing whether the multiple conditions are met.

(4) Column with value to return.

In the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula, I specify the column with the value to return:

• With the third argument (return_array) of the XLOOKUP function.
• As a single-column (1 column, several rows) cell range: I7:I26.

In the VLOOKUP multiple criteria example formula, I specify the following:

• The table (cell range) where I look in (C7:I26) with the second argument (table_array) of the VLOOKUP function. This table contains the column with the value to return.
• The number of the column (inside the table where I look in) with the value to return using the third argument (col_index_num) of the VLOOKUP function: 7.

(5) Exact match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. I don't explicitly specify the match mode (exact vs. approximate) in the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula.

The VLOOKUP function searches (by default) for an approximate match of the lookup value (also known as a range lookup). To make the VLOOKUP function (in the VLOOKUP multiple criteria example formula) search for an exact match, I set the fourth argument of the VLOOKUP function (range_lookup) to FALSE.

Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator) vs. INDEX MATCH Multiple Criteria Example Formulas

The following 2 example formulas are equivalents.

• I explain the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula above.
• The INDEX MATCH multiple criteria example formula is an equivalent example formula working with the INDEX and MATCH functions (vs. the XLOOKUP function).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/

'XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula
=XLOOKUP(L6&L7,A7:A26&B7:B26,I7:I26)

'INDEX MATCH multiple criteria example formula
=INDEX(I7:I26,MATCH(1,(L6=A7:A26)*(L7=B7:B26),0))

Notice the following (main) similarities and differences in how I specify the main formula components:

(1) Formula structure.

I use a single function (XLOOKUP) to create the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula.

I use 2 functions (INDEX and MATCH) to create the INDEX MATCH multiple criteria example formula.

(2) Lookup value.

In both cases, I use the first argument of the applicable function (lookup_value) to specify the lookup value.

• In the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula, lookup_value is the first argument of the XLOOKUP function.
• In the INDEX MATCH multiple criteria example formula, lookup_value is the first argument of the MATCH function.

In the XLOOKUP multiple criteria example formula, I set this first argument (lookup_value) to a text value concatenating the 2 criteria I consider:

• Salesperson, stored in cell L6; and
• Date, stored in cell L7.

I use the ampersand (&) text concatenation operator to create this concatenated text value: L6&L7.

In the INDEX MATCH multiple criteria example formula, I set this lookup value to 1.

The MATCH function searches for this value (1) in the array I specify as second argument (lookup_array) of the MATCH function. This array contains 0s and 1s, depending on whether all applicable criteria are met (or not) by all applicable columns in the applicable row.

(3) Array where I search.

In the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula, I:

• Use the second argument of the XLOOKUP function (lookup_array) to specify the array where I search; and
• Use the ampersand (&) text concatenation operator to create an array with the contents stored in the cell ranges with the 2 criteria I consider.
  • Column A (cells A7 to A26; A7:A26); and
  • Column B (cells B7 to B26; B7:B26).

In the INDEX MATCH multiple criteria example formula, I:

• Use the second argument of the MATCH function (lookup_array) to specify the array where I search; and
• Create an array of 0s and 1s, where each 0 or 1 (as applicable) indicates whether all multiple conditions are met (or not) in a specific row of the table where I search with the INDEX MATCH multiple criteria example formula.

The INDEX MATCH multiple criteria example formula considers 2 conditions:

• Condition 1: The salesperson (in column A, cells A7 to A26 or “A7:A26”) is equal to the salesperson in cell L6 (L6=A7:A26).
• Condition 2: The date (in column B, cells B7 to B26 or “B7:B26”) is equal to the date in cell L7 (L7=B7:B26).

(4) Column with value to return.

In both cases, I use a single function argument to specify the column with the value to return as a single-column (1 column, several rows) cell range: I7:I26.

• In the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula, I use the third argument (return_array) of the XLOOKUP function.
• In the INDEX MATCH multiple criteria example formula, I use the first argument (array) of the INDEX function.

(5) Exact match.

The XLOOKUP function searches (by default) for an exact match of the lookup value. I don't explicitly specify the match mode (exact vs. approximate) in the XLOOKUP multiple criteria (with the ampersand text concatenation operator) example formula.

The MATCH function searches (by default) for the largest value that is less than or equal to the lookup value. To make the MATCH function (in the INDEX MATCH multiple criteria example formula) search for an exact match, I set the third argument of the MATCH function (match_type) to 0.

(5) Excel XLOOKUP Multiple Criteria (with Boolean Logic)

In this Section, you learn how to create an XLOOKUP multiple criteria (with Boolean logic) formula, where the XLOOKUP function:

• Considers multiple criteria; and
• Relies on Boolean logic to test whether the multiple criteria are met.

Excel XLOOKUP Multiple Criteria (with Boolean Logic) Formula Template/Structure

The following is the Excel XLOOKUP multiple criteria (with Boolean logic) formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(Criterion1[ComparisonOperator]ColumnOrRowWithCriterion1)*(Criterion2[ComparisonOperator]ColumnOrRowWithCriterion2)*…*(Criterion#[ComparisonOperator]ColumnOrRowWithCriterion#),ColumnsOrRowsWithValuesToReturn)

Step-by-Step Process to Set Up an Excel XLOOKUP Multiple Criteria (with Boolean Logic) Formula

Do the following to create an Excel XLOOKUP multiple criteria (with Boolean logic) formula:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Set the first argument of the XLOOKUP function (lookup_value) to 1.

lookup_value is the value you search for (the lookup value) in the array of 0s and 1s you create in step #3.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,

(3) Set up the multiple conditions.

Use the second argument of the XLOOKUP function (lookup_array) to create an expression that does the following:

• Evaluate multiple conditions; and
• Return a single value for each row (when doing a VLookup) or column (when doing an HLookup) in the table where you search with the XLOOKUP multiple criteria (with Boolean logic) formula, indicating whether all multiple conditions are met (or not) in that specific row (when doing a VLookup) or column (when doing an HLookup).

Work with logical expressions to set up the multiple conditions in an XLOOKUP multiple criteria (with Boolean logic) formula. Logical expressions:

• Return a Boolean value (TRUE or FALSE) when evaluated; and
• Allow you to test whether a set of multiple conditions is met.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(Criterion1[ComparisonOperator]ColumnOrRowWithCriterion1)*(Criterion2[ComparisonOperator]ColumnOrRowWithCriterion2)*…*(Criterion#[ComparisonOperator]ColumnOrRowWithCriterion#),

This expression template/structure works with the following 2 operators to test whether a set of multiple conditions is met:

• 1 or more comparison operators.
  • Comparison operators:
    • Carry out comparisons; and
    • Return a Boolean value (TRUE or FALSE).
  • The following are commonly used comparison operators:
    • Less than (<).
    • Less than or equal to (<=).
    • Greater than (>).
    • Greater than or equal to (>=).
    • Equal to (=).
    • Not equal to (<>).
• The multiplication operator: *.
  • As a general rule, multiplies 2 numbers.
  • When multiplying Boolean values (TRUE and FALSE), converts the Boolean values into 0s and 1s as follows:
    • FALSE is converted to 0.
    • TRUE is converted to 1.
  • When creating an XLOOKUP multiple criteria (with Boolean logic) formula, the multiplication operator (*) acts as the AND logical operator. In other words: The entire expression returns 0 (FALSE) or 1 (TRUE) as follows:
    • 0 (FALSE) if any (or all) evaluated condition(s) return 0 (FALSE).
    • 1 (TRUE) if all evaluated conditions return 1 (TRUE).

(4) Specify the third argument of the XLOOKUP function: return_array.

return_array is the column(s) (when doing a VLookup) or row(s) (when doing an HLookup) with the value(s) to return. The number of columns (when doing a VLookup) or rows (when doing an HLookup) of the cell range you specify as return_array should be equal to the number of values to return.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the cell range you specify as return_array is the same as that of the cell ranges you worked with to specify the lookup_array (in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(Criterion1[ComparisonOperator]ColumnOrRowWithCriterion1)*(Criterion2[ComparisonOperator]ColumnOrRowWithCriterion2)*…*(Criterion#[ComparisonOperator]ColumnOrRowWithCriterion#),ColumnsOrRowsWithValuesToReturn)

Additional Cues for Excel XLOOKUP Multiple Criteria (with Boolean Logic)

(1) The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

(2) By default, XLOOKUP:

• Searches for an exact match of the lookup value.
• Works with the first entry matching the lookup value.
• Returns the #N/A error if it fails to find a valid match for the lookup value.

(3) Carry out as many comparison tests (with the applicable comparison operator) as needed (1 comparison test per criterion) to compare each of the multiple criteria against the cells in a column (when doing a VLookup) or row (when doing an HLookup) with the respective criterion.

(4) Specify the column (when doing a VLookup) or row (when doing an HLookup) with a criterion (ColumnOrRowWithCriterion#) as:

• A single-column (1 column, several rows) cell range, when doing a VLookup; or
• A single-row (1 row, several columns) cell range, when doing an HLookup.

(5) The XLOOKUP multiple criteria (with Boolean logic) formula template/structure I explain above isn't the only formula template/structure you can use to create an XLOOKUP multiple criteria formula.

Strictly speaking: You can work with the ampersand (&) text concatenation operator to specify XLOOKUP's first 2 arguments (lookup_value and lookup_array) when creating an XLOOKUP multiple criteria (with ampersand text concatenation operator) formula.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(Criterion1&Criterion2&…&Criterion#,ColumnOrRowWithCriterion1&ColumnOrRowWithCriterion2&…&ColumnOrRowWithCriterion#,ColumnsOrRowsWithValuesToReturn)

The XLOOKUP multiple criteria (with Boolean logic) formula template/structure I explain above is (as a general rule) more powerful and flexible (vs. this XLOOKUP multiple criteria (with ampersand text concatenation operator) formula template/structure). Among others, the XLOOKUP multiple criteria (with Boolean logic) formula template/structure is easier to implement when:

• Carrying out more complex lookups; or
• Working with additional constructs, such as additional operators and worksheet functions.

Please refer to the Section on Excel XLOOKUP Multiple Criteria (with the Ampersand Text Concatenation Operator) for a more detailed explanation on how to create an XLOOKUP multiple criteria (with the ampersand text concatenation operator) formula, where the XLOOKUP function:

• Considers multiple criteria; and
• Relies on the ampersand (&) text concatenation operator to specify the multiple criteria.

Excel XLOOKUP Multiple Criteria (with Boolean Logic) Example Worksheet

This Excel XLOOKUP Tutorial is accompanied by an Excel workbook with the data and formulas I use in the examples inside this Tutorial (including this example). Get this example workbook (for free) by clicking the button below.

The example worksheet (Multiple Criteria Boolean) has 3 tables/sections with the following characteristics:

(1) Table 1 (cells A6 to I26).

The table:

• With the data.
• Where I search with the XLOOKUP multiple criteria (with Boolean logic) example formula.

Main characteristics:

• 9 columns (Salesperson, Date, VLOOKUP Helper Column, Customer, Product, City, Units, Unit Price, Total Sales).
• 1 header row (row 6).
• 20 entries (rows 7 to 26).

(2) Multiple Criteria (cells K6 to L7).

2 criteria (Salesperson and Date) are stored in cells:

• L6 (Salesperson).
• L7 (Date).

(3) Table 2 (cells K9 to M12).

The table where I:

• Set up the following example formulas:
  • The XLOOKUP multiple criteria (with Boolean logic) example formula; and
  • 2 equivalent example formulas, working with different function(s):
    • VLOOKUP.
    • INDEX MATCH.
• Display the results.

This table has 3 columns:

• Function(s): Column K (cells K9 to K12) lists the function(s) used by the example formulas I enter in column L.
• Total Sales: Column L (cells L9 to L12):
  • Stores the example formulas:
    • Cell L10 stores the XLOOKUP multiple criteria (with Boolean logic) example formula.
    • Cell L11 stores an equivalent VLOOKUP multiple criteria example formula.
    • Cell L12 stores an equivalent INDEX MATCH multiple criteria example formula.
  • Displays the results.
• Formula: Column M (cells M9 to M12) displays the example formulas I enter in column L.

I use the VLOOKUP multiple criteria and INDEX MATCH multiple criteria example formulas:

• For illustrative purposes.
• To compare XLOOKUP with 2 commonly used alternatives:
  • VLOOKUP (XLOOKUP vs. VLOOKUP).
  • INDEX MATCH (XLOOKUP vs. INDEX MATCH).

Excel XLOOKUP Multiple Criteria (with Boolean Logic) Example Formula

I create the XLOOKUP multiple criteria (with Boolean logic) example formula as follows:

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(

(2) Set the first argument of the XLOOKUP function (lookup_value) to 1.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,

(3) Set up the multiple conditions.

The XLOOKUP multiple criteria (with Boolean logic) example formula considers 2 conditions:

Condition 1: The salesperson (in column A, cells A7 to A26 or “A7:A26”) is equal to the salesperson in cell L6.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
(L6=A7:A26)

Condition 2: The date (in column B, cells B7 to B26 or “B7:B26”) is equal to the date in cell L7.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
(L7=B7:B26)

Therefore:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(L6=A7:A26)*(L7=B7:B26),

(4) Specify the third argument of the XLOOKUP function: The column (I do a VLookup) with the value to return.

The column with the value to return is column I:

• Cells I7 to I26; or
• I7:I26.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(L6=A7:A26)*(L7=B7:B26),I7:I26)