Excel XLOOKUP: Examples to Use the XLOOKUP Function in 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.

Table of Contents

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).

Example worksheet for Excel XLOOKUP exact 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 exact match example formula

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)

Excel XLOOKUP exact match vs. VLOOKUP exact match example formulas

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))

Excel XLOOKUP exact match vs INDEX MATCH exact match example formulas

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.

'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).

Example worksheet for Excel XLOOKUP approximate match

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 approximate match example formula

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)

Excel XLOOKUP approximate match vs. VLOOKUP approximate match example formulas

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))

Excel XLOOKUP approximate match vs. INDEX MATCH approximate match example formulas

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 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.

'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).

Example worksheet for Excel XLOOKUP return multiple values (columns)

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 return multiple values (columns) example formula

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)

Excel XLOOKUP vs. VLOOKUP return multiple values (columns) example formulas

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})

Excel XLOOKUP vs. INDEX MATCH return multiple values (columns) example formulas

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 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 concatenation operator) example worksheet

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 concatenation operator) example formula

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)

Excel XLOOKUP vs VLOOKUP multiple criteria (with the ampersand concatenation operator) example formulas

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))

Excel XLOOKUP vs. INDEX MATCH multiple criteria (with the ampersand concatenation operator) example formulas

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 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).

Example: Array for XLOOKUP multiple criteria (with Boolean logic) example formula

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

'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

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).

Example worksheet for Excel XLOOKUP multiple criteria (with Boolean logic)

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)

Excel XLOOKUP multiple criteria (with Boolean logic) example formula

Excel XLOOKUP Multiple Criteria (with Boolean Logic) vs. VLOOKUP Multiple Criteria Example Formulas

The following 2 example formulas are equivalents.

I explain the XLOOKUP multiple criteria (with Boolean logic) 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 Boolean logic) example formula
=XLOOKUP(1,(L6=A7:A26)*(L7=B7:B26),I7:I26)

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

Excel XLOOKUP multiple criteria (with Boolean logic) vs. VLOOKUP multiple criteria example formulas

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

(1) Solution structure.

The XLOOKUP multiple criteria (with Boolean logic) 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).

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 the XLOOKUP multiple criteria (with Boolean logic) example formula, I set the first argument of the XLOOKUP function (lookup_value) to 1.

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

In the VLOOKUP multiple criteria example formula, I set the first argument of the VLOOKUP function (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 Boolean logic) example formula, I:

Use the second argument of the XLOOKUP 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 XLOOKUP multiple criteria (with Boolean logic) example formula.

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 (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).

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.

(4) Column with value to return.

In the XLOOKUP multiple criteria (with Boolean logic) 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.

Excel XLOOKUP Multiple Criteria (with Boolean Logic) vs. INDEX MATCH Multiple Criteria Example Formulas

The following 2 example formulas are equivalents.

I explain the XLOOKUP multiple criteria (with Boolean logic) 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 Boolean logic) example formula
=XLOOKUP(1,(L6=A7:A26)*(L7=B7:B26),I7:I26)

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

Excel XLOOKUP multiple criteria (with Boolean logic) vs. INDEX MATCH multiple criteria example formulas

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 Boolean logic) 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 Boolean logic) 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.
Set this lookup value to 1.

Both functions (XLOOKUP and MATCH) search for this value (1) in the array I specify as second argument (lookup_array) of the applicable 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 both cases, I:

Use the second argument of the applicable function (lookup_array) to specify the array where I search.
- In the XLOOKUP multiple criteria (with Boolean logic) example formula, lookup_array is the second argument of the XLOOKUP function.
- In the INDEX MATCH multiple criteria example formula, lookup_array is the second argument of the MATCH function.
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 XLOOKUP multiple criteria (with Boolean logic) or INDEX MATCH multiple criteria example formulas (as applicable).

The XLOOKUP multiple criteria (with Boolean logic) and INDEX MATCH multiple criteria example formulas consider 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 Boolean logic) 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.

(6) Excel XLOOKUP Two-Way Lookup

In this Section, you learn how to create an XLOOKUP two-way lookup formula, where the XLOOKUP function considers criteria on (both):

1 column; and
1 row.

Excel XLOOKUP Two-Way Lookup Formula Template/Structure

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValueColumn,ColumnWhereYouSearch,XLOOKUP(LookupValueRow,RowWhereYouSeach,TableYouWorkWith))

Step-by-Step Process to Set Up an Excel XLOOKUP Two-Way Lookup Formula

Do the following to create an Excel XLOOKUP two-way lookup formula:

(1) Call the XLOOKUP function.

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

(2) Use the first argument of the XLOOKUP function (lookup_value) to specify the value you search for in the applicable column (you identify in step #3): The first lookup value.

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

(3) Use the second argument of the XLOOKUP function (lookup_array) to identify the column where you search for the first lookup value (you specified in step #2).

As a general rule: When creating an XLOOKUP two-way lookup formula, specify this first lookup_array as a single-column (1 column, several rows) cell range.

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

(4) Call the XLOOKUP function (again).

Nest a (second) XLOOKUP function as the third argument (return_array) of the first (outer) XLOOKUP function (you called in step #1).

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

(5) Use the first argument of the second (nested) XLOOKUP function (lookup_value) to specify the value you search for in the applicable row (you identify in step #6): The second lookup value.

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

(6) Use the second argument of the second (nested) XLOOKUP function (lookup_array) to identify the row where you search for the second lookup value (you specified in step #5).

As a general rule: When creating an XLOOKUP two-way lookup formula, specify this second lookup_array as a single-row (1 row, several columns) cell range).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValueColumn,ColumnWhereYouSearch,XLOOKUP(LookupValueRow,RowWhereYouSeach,

(7) Identify the full table you work with (the cell range with the applicable data). Use the third argument of the second (nested) XLOOKUP function (return_array) to do this.

Ensure:

The number of rows of the cell range you specify as return_array is the same as that of the cell range you specified as first lookup_array (in step #3); and
The number of columns of the cell range you specify as return_array is the same as that of the cell range you specified as second lookup_array (in step #6).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(LookupValueColumn,ColumnWhereYouSearch,XLOOKUP(LookupValueRow,RowWhereYouSeach,TableYouWorkWith))

Additional Cues for Excel XLOOKUP Two-Way Lookup

(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) The XLOOKUP two-way lookup formula template/structure I explain above uses 2 XLOOKUP functions as follows:

The first (outer) XLOOKUP function searches in a column (does a VLookup).
The second (nested) XLOOKUP function:
- Searches in a row (does an HLookup); and
- Returns an array with the items stored in the applicable column.

The array returned by the second (nested) XLOOKUP function is passed as the return array for the first (outer) XLOOKUP function. The first (outer) XLOOKUP function (then) returns the applicable value from this array.

Your XLOOKUP two-way lookup formula can follow the opposite (but equivalent) process. In other words: You can create an XLOOKUP two-way lookup formula that uses the 2 XLOOKUP functions as follows:

The first (outer) XLOOKUP function searches in a row (does an HLookup); and
The second (nested) XLOOKUP function searches in a column (does a VLookup).

You can apply the principles I explain above to create such an XLOOKUP two-way lookup formula.

Excel XLOOKUP Two-Way Lookup Example Worksheet

The example worksheet (Two-Way Lookup) 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 two-way lookup 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) Multiple Criteria (cells J6 to K7).

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

K6: Salesperson. The criteria I search for in the applicable column.
K7: Units. The criteria I search for in the applicable row.

(3) Table 2 (cells J9 to L12).

The table where I:

Set up the following example formulas:
- The XLOOKUP two-way lookup 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 J9 to J12) lists the function(s) used by the example formulas I enter in column K.
Units: Column K (cells K9 to K12):
- Stores the example formulas:
  - Cell K10 stores the XLOOKUP two-way lookup example formula.
  - Cell K11 stores an equivalent VLOOKUP two-way lookup example formula.
  - Cell K12 stores an equivalent INDEX MATCH two-way lookup example formula.
- Displays the results.
Formula: Column L (cells L9 to L12) displays the example formulas I enter in column K.

I use the VLOOKUP two-way lookup and INDEX MATCH two-way lookup example formulas:

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

Example worksheet for Excel XLOOKUP two-way lookup

Excel XLOOKUP Two-Way Lookup Example Formula

I create the XLOOKUP two-way lookup example formula as follows:

(1) Call the XLOOKUP function.

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

(2) Use the first argument of the XLOOKUP function to specify the value I search for in the applicable column (I identify in step #3): The first lookup value.

The first lookup value is stored in cell K6.

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

(3) Use the second argument of the XLOOKUP function to identify the column where I search for the first lookup value (I specified in step #2).

I search for the first lookup value in column A:

Cells A6 to A26; or
A6:A26.

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

(4) Call the XLOOKUP function (again).

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

(5) Use the first argument of the second (nested) XLOOKUP function to specify the value I search for in the applicable row (I identify in step #6): The second lookup value.

The second lookup value is stored in cell K7.

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

(6) Use the second argument of the second (nested) XLOOKUP function to identify the row where I search for the second lookup value (I specified in step #5).

I search for the first lookup value in row 6:

Cells A6 to H6; or
A6:H6.

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

(7) Identify the full table I work with.

The full table I work with (the cell range with the applicable data) is stored in:

Cells A6 to H26; or
A6:H26.

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

Excel XLOOKUP two-way lookup example formula

Excel XLOOKUP vs. VLOOKUP Two-Way Lookup Example Formulas

The following 2 example formulas are equivalents.

I explain the XLOOKUP two-way lookup example formula above.
The VLOOKUP two-way lookup 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 two-way lookup example formula
=XLOOKUP(K6,A6:A26,XLOOKUP(K7,A6:H6,A6:H26))

'VLOOKUP two-way lookup example formula
=VLOOKUP(K6,A6:H26,MATCH(K7,A6:H6,0),FALSE)

Excel XLOOKUP vs. VLOOKUP two-way lookup example formulas

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

(1) Formula structure.

I use 2 functions to create each of the example formulas:

2 XLOOKUP functions to create the XLOOKUP two-way lookup example formula.
1 VLOOKUP function and 1 MATCH function to create the VLOOKUP two-way lookup example formula.

(2) Lookup value.

In both cases, I use the first argument of the applicable functions (lookup_value) to specify the lookup values (the values stored in cells K6 and K7).

In the XLOOKUP two-way lookup example formula, lookup_value is the first argument of (both) XLOOKUP functions.
In the VLOOKUP two-way lookup example formula, lookup_value is the first argument of (both):
- The VLOOKUP function; and
- The MATCH function.

(3) Column and row where I search.

In the XLOOKUP two-way lookup example formula, I specify the column and row where I search:

With the second argument (lookup_array) of the XLOOKUP functions.
As one of the following (as applicable):
- A single-column (1 column, several rows) cell range (A6:A26); or
- A single-row (1 row, several columns) cell range (A6:H6).

In the VLOOKUP two-way lookup example formula, I specify the following:

(3.1.) The table (cell range) where I look in:

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

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

Column A.
Cells A6 to A26.

(3.2.) The row where I search:

With the second argument (lookup_array) of the MATCH function.
As a single-row (1 row, several columns) cell range (A6:H6).

(4) Full table I work with.

In both cases, I use the applicable argument to specify the full table I work with (the cell range with the applicable data): A6:H26.

In the XLOOKUP two-way lookup example formula, I use the third argument (return_array) of the second (nested) XLOOKUP function.
In the VLOOKUP two-way lookup example formula, I use the second argument of the VLOOKUP function (table_array).

(5) Column with value to return.

In the XLOOKUP two-way lookup example formula, I specify the column with the value to return:

With the third argument (return_array) of the first (outer) XLOOKUP function.
As the array returned by the second (nested) XLOOKUP function.

The second (nested) XLOOKUP function does the following:

Search for the applicable (second) lookup value (Units, stored in cell K7) in the row where I search (row 6, cells A6 to H6); and
Return an array with the items stored in the corresponding column (column F; cells F6 to F26).

Column I (cells I6 to I26) in the following image illustrates the array returned by the second (nested) XLOOKUP function.

Excel XLOOKUP two-way lookup column array

In the VLOOKUP two-way lookup example formula, I specify the following:

The full table I work with (cells A6 to H26) with the second argument (table_array) of the VLOOKUP function.
The number of the column (inside the table I work with) with the value to return:
- Using the third argument (col_index_num) of the VLOOKUP function.
- As the number returned by the MATCH function.

The MATCH function does the following:

Search for the applicable (second) lookup value (Units, stored in cell K7) in the row where I search (row 6, cells A6 to H6); and
Return the relative position of the corresponding item (Units) in that row (cells A6 to H6).

(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 two-way lookup 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 two-way lookup example formula) search for an exact match, I set the fourth argument of the VLOOKUP function (range_lookup) to FALSE.

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 VLOOKUP two-way lookup example formula) search for an exact match, I set the third argument of the MATCH function (match_type) to 0.

Excel XLOOKUP vs. INDEX MATCH Two-Way Lookup Example Formulas

The following 2 example formulas are equivalents.

I explain the XLOOKUP two-way lookup example formula above.
The INDEX MATCH two-way lookup 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 two-way lookup example formula
=XLOOKUP(K6,A6:A26,XLOOKUP(K7,A6:H6,A6:H26))

'INDEX MATCH two-way lookup example formula
=INDEX(A6:H26,MATCH(K6,A6:A26,0),MATCH(K7,A6:H6,0))

Excel XLOOKUP vs. INDEX MATCH two-way lookup example formulas

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

(1) Formula structure.

I use several functions to create each of the example formulas:

2 XLOOKUP functions to create the XLOOKUP two-way lookup example formula.
1 INDEX function and 2 MATCH functions to create the INDEX MATCH two-way lookup example formula.

(2) Lookup value.

In both cases, I use the first argument of the applicable functions (lookup_value) to specify the lookup values (the values stored in cells K6 and K7).

In the XLOOKUP two-way lookup example formula, lookup_value is the first argument of (both) XLOOKUP functions.
In the INDEX MATCH two-way lookup example formula, lookup_value is the first argument of (both) MATCH functions.

(3) Column and row where I search.

In both cases, I use the second argument of the applicable function (lookup_array) to specify the column and row where I search as (as applicable):

A single-column (1 column, several rows) cell range (A6:A26); or
A single-row (1 row, several columns) cell range (A6:H6).

In the XLOOKUP two-way lookup example formula, lookup_array is the second argument of (both) XLOOKUP functions.

In the INDEX MATCH two-way lookup example formula, lookup_array is the second argument of (both) MATCH functions.

(4) Full table I work with.

In both cases, I use the applicable argument to specify the full table I work with (the cell range with the applicable data): A6:H26.

In the XLOOKUP two-way lookup example formula, I use the third argument (return_array) of the second (nested) XLOOKUP function.
In the INDEX MATCH two-way lookup example formula, I use the first argument of the INDEX function (array).

(5) Column with value to return.

In the XLOOKUP two-way lookup example formula, I specify the column with the value to return:

With the third argument (return_array) of the first (outer) XLOOKUP function.
As the array returned by the second (nested) XLOOKUP function.

The second (nested) XLOOKUP function does the following:

Search for the applicable (second) lookup value (Units, stored in cell K7) in the row where I search (row 6, cells A6 to H6); and
Return an array with the items stored in the corresponding column (column F; cells F6 to F26).

Column I (cells I6 to I26) in the following image illustrates the array returned by the second (nested) XLOOKUP function.

In the INDEX MATCH two-way lookup example formula, I specify the following:

The full table I work with (cells A6 to H26) with the first argument (array) of the INDEX function.
The number of the column (inside the table I work with) with the value to return:
- Using the third argument (column_num) of the INDEX function.
- As the number returned by the MATCH function.

The MATCH function does the following:

Search for the applicable (second) lookup value (Units, stored in cell K7) in the row where I search (row 6, cells A6 to H6); and
Return the relative position of the corresponding item (Units) in that row (cells A6 to H6).

(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 two-way lookup 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 two-way lookup example formula) search for an exact match, I set the third argument of the MATCH function (match_type) to 0.

(7) Excel XLOOKUP Multiple Criteria Column and Row

In this Section, you learn how to create an XLOOKUP multiple criteria column and row formula, where the XLOOKUP function considers multiple criteria on (both):

Columns; and
Rows.

Excel XLOOKUP Multiple Criteria Column and Row Formula Template/Structure

The following is the Excel XLOOKUP multiple criteria column and row 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,(ColumnCriterion1[ComparisonOperator]ColumnWithCriterion1)*(ColumnCriterion2[ComparisonOperator]ColumnWithCriterion2)*…*(ColumnCriterion#[ComparisonOperator]ColumnWithCriterion#),XLOOKUP(1,(RowCriterion1[ComparisonOperator]RowWithCriterion1)*(RowCriterion2[ComparisonOperator]RowWithCriterion2)*…*(RowCriterion#[ComparisonOperator]RowWithCriterion#),TableYouWorkWith))

Step-by-Step Process to Set Up an Excel XLOOKUP Multiple Criteria Column and Row Formula

Do the following to create an Excel XLOOKUP multiple criteria column and row 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 column conditions for your search (VLookup) in the applicable columns.

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

Evaluate multiple conditions (in the applicable columns); and
Return a single value for each row in the table where you search with the XLOOKUP multiple criteria column and row formula, indicating whether all multiple conditions are met (or not) in that specific row.

Work with logical expressions to set up the multiple column conditions in an XLOOUP multiple criteria column and row 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,(ColumnCriterion1[ComparisonOperator]ColumnWithCriterion1)*(ColumnCriterion2[ComparisonOperator]ColumnWithCriterion2)*…*(ColumnCriterion#[ComparisonOperator]ColumnWithCriterion#),

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 rows and columns 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).

Excel XLOOKUP multiple criteria column and row 0s and 1s array example

(4) Call the XLOOKUP function (again).

Nest a (second) XLOOKUP function as the third argument (return_array) of the first (outer) XLOOKUP function (you called in step #1).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(ColumnCriterion1[ComparisonOperator]ColumnWithCriterion1)*(ColumnCriterion2[ComparisonOperator]ColumnWithCriterion2)*…*(ColumnCriterion#[ComparisonOperator]ColumnWithCriterion#),XLOOKUP(

(5) Set the first argument of the second (nested) 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 #6.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(ColumnCriterion1[ComparisonOperator]ColumnWithCriterion1)*(ColumnCriterion2[ComparisonOperator]ColumnWithCriterion2)*…*(ColumnCriterion#[ComparisonOperator]ColumnWithCriterion#),XLOOKUP(1,

(6) Set up the multiple row conditions for your search (HLookup) in the applicable rows.

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

Evaluate multiple conditions (in the applicable rows); and
Return a single value for each column in the table where you search with the XLOOKUP multiple criteria column and row formula, indicating whether all multiple conditions are met (or not) in that specific column.

Follow the process and principles I describe in step #3 to set up the multiple row conditions in an XLOOKUP multiple criteria column and row formula. In other words: Work with:

Logical expressions;
1 or more comparison operators; and
The multiplication operator (*).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(ColumnCriterion1[ComparisonOperator]ColumnWithCriterion1)*(ColumnCriterion2[ComparisonOperator]ColumnWithCriterion2)*…*(ColumnCriterion#[ComparisonOperator]ColumnWithCriterion#),XLOOKUP(1,(RowCriterion1[ComparisonOperator]RowWithCriterion1)*(RowCriterion2[ComparisonOperator]RowWithCriterion2)*…*(RowCriterion#[ComparisonOperator]RowWithCriterion#),

(7) Identify the full table you work with (the cell range with the applicable data). Use the third argument of the second (nested) XLOOKUP function (return_array) to do this.

Ensure:

The number of rows of the cell range you specify as return_array is the same as that of the cell ranges you used to specify the multiple column conditions (in step #3); and
The number of columns of the cell range you specify as return_array is the same as that of the cell ranges you used to specify the multiple row conditions (in step #6).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(ColumnCriterion1[ComparisonOperator]ColumnWithCriterion1)*(ColumnCriterion2[ComparisonOperator]ColumnWithCriterion2)*…*(ColumnCriterion#[ComparisonOperator]ColumnWithCriterion#),XLOOKUP(1,(RowCriterion1[ComparisonOperator]RowWithCriterion1)*(RowCriterion2[ComparisonOperator]RowWithCriterion2)*…*(RowCriterion#[ComparisonOperator]RowWithCriterion#),TableYouWorkWith))

Additional Cues for Excel XLOOKUP Multiple Criteria Column and Row

(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 (as applicable) the column or row with the respective criterion.

(4) Specify the column with a criterion (ColumnWithCriterion#) as a single-column (1 column, several rows) cell range.

Specify the row with a criterion (RowWithCriterion#) as a single-row (1 row, several columns) cell range.

(5) The XLOOKUP multiple criteria column and row formula template/structure I explain above uses 2 XLOOKUP functions as follows:

The first (outer) XLOOKUP function searches in multiple columns (does a multiple criteria VLookup).
The second (nested) XLOOKUP function:
- Searches in multiple rows (does a multiple criteria HLookup); and
- Returns an array with the items stored in the applicable column.

Your XLOOKUP multiple criteria column and row formula can follow the opposite (but equivalent) process. In other words: You can create an XLOOKUP multiple criteria column and row formula that uses the 2 XLOOKUP functions as follows:

The first (outer) XLOOKUP function searches in multiple rows (does a multiple criteria HLookup); and
The second (nested) XLOOKUP function searches in multiple columns (does a multiple criteria VLookup).

You can apply the principles I explain above to create such an XLOOKUP multiple criteria column and row formula.

(6) The XLOOKUP multiple criteria column and row formula template/structure I explain above isn't the only formula template/structure you can use to create an XLOOKUP multiple criteria column and row formula.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(ColumnCriterion1&ColumnCriterion2&...&ColumnCriterion#,ColumnWithCriterion1&ColumnWithCriterion2&...&ColumnWithCriterion#,XLOOKUP(RowCriterion1&RowCriterion2&...&RowCriterion#,RowWithCriterion1&RowWithCriterion2&...&RowWithCriterion#,TableYouWorkWith))

The XLOOKUP multiple criteria column and row (with Boolean logic) formula template/structure I explain above is (as a general rule) more powerful and flexible (vs. this XLOOKUP multiple criteria column and row (with ampersand text concatenation operator) formula template/structure). Among others, the XLOOKUP multiple criteria column and row (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.

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

Excel XLOOKUP Multiple Criteria Column and Row Example Worksheet

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

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

The table:

With the data.
Where I search with the XLOOKUP multiple criteria column and row example formula.

Main characteristics:

9 columns (Salesperson, Date, VLOOKUP Helper Column, Customer, Product, City, Units, Unit Price, Total Sales).
2 header rows (rows 6 and 7).
20 entries (rows 8 to 27).

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

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

L6 (Salesperson).
L7 (Date).

2 row criteria (Forecast/Actual and Item) are stored in cells:

L8 (Forecast/Actual).
L9 (Item).

(3) Table 2 (cells K11 to M14).

The table where I:

Set up the following example formulas:
- The XLOOKUP multiple criteria column and row 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 K12 to K14) lists the function(s) used by the example formulas I enter in column L.
Units: Column L (cells L12 to L14):
- Stores the example formulas:
  - Cell L12 stores the XLOOKUP multiple criteria column and row example formula.
  - Cell L13 stores an equivalent VLOOKUP multiple criteria column and row example formula.
  - Cell L14 stores an equivalent INDEX MATCH multiple criteria column and row example formula.
- Displays the results.
Formula: Column M (cells M12 to M14) 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 column and row example worksheet

Excel XLOOKUP Multiple Criteria Column and Row Example Formula

I create the XLOOKUP multiple criteria column and row 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 column conditions.

The XLOOKUP multiple criteria column and row example formula considers 2 column 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=A6:A27)

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=B6:B27)

Therefore:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(L6=A6:A27)*(L7=B6:B27),

(4) Call the XLOOKUP function (again).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(L6=A6:A27)*(L7=B6:B27),XLOOKUP(

(5) Set the first argument of the second (nested) XLOOKUP function (lookup_value) to 1.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(L6=A6:A27)*(L7=B6:B27),XLOOKUP(1,

(6) Set up the multiple row conditions.

The XLOOKUP multiple criteria column and row example formula considers 2 row conditions:

Condition 1: The first column header (in row 6, cells A6 to I6 or “A6:I6”) is equal to the “Forecast” or “Actual”, as specified in cell L8.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
(L8=A6:I6)

Condition 2: The second column header (in row 7, cells A7 to I7 or “A7:I7”) is equal to the item in cell L9.

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

Therefore:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(L6=A6:A27)*(L7=B6:B27),XLOOKUP(1,(L8=A6:I6)*(L9=A7:I7),

(7) Identify the full table I work with.

The full table I work with (the cell range with the applicable data) is stored in:

Cells A6 to I27; or
A6:I27.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup/
=XLOOKUP(1,(L6=A6:A27)*(L7=B6:B27),XLOOKUP(1,(L8=A6:I6)*(L9=A7:I7),A6:I27))

Excel XLOOKUP multiple criteria column and row example formula

Excel XLOOKUP vs. VLOOKUP Multiple Criteria Column and Row Example Formulas

The following 2 example formulas are equivalents.

I explain the XLOOKUP multiple criteria column and row example formula above.
The VLOOKUP multiple criteria column and row 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 column and row example formula
=XLOOKUP(1,(L6=A6:A27)*(L7=B6:B27),XLOOKUP(1,(L8=A6:I6)*(L9=A7:I7),A6:I27))

'VLOOKUP multiple criteria column and row example formula
=VLOOKUP(L6&L7,C6:I27,MATCH(1,(L8=C6:I6)*(L9=C7:I7),0),FALSE)

Excel XLOOKUP vs. VLOOKUP multiple criteria column and row example formulas

Excel XLOOKUP vs. INDEX MATCH Multiple Criteria Column and Row Example Formulas

The following 2 example formulas are equivalents.

I explain the XLOOKUP multiple criteria column and row example formula above.
The INDEX MATCH multiple criteria column and row 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 column and row example formula
=XLOOKUP(1,(L6=A6:A27)*(L7=B6:B27),XLOOKUP(1,(L8=A6:I6)*(L9=A7:I7),A6:I27))

'INDEX MATCH multiple criteria column and row example formula
=INDEX(A6:I27,MATCH(1,(L6=A6:A27)*(L7=B6:B27),0),MATCH(1,(L8=A6:I6)*(L9=A7:I7),0))

Excel XLOOKUP vs. INDEX MATCH multiple criteria column and row example formulas

Download the Excel XLOOKUP Example Workbook

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

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 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.

Related Excel Training Materials and Resources

(1) Excel XLOOKUP Exact Match

Excel XLOOKUP Exact Match Formula Template/Structure

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

Additional Cues for Excel XLOOKUP Exact Match

Excel XLOOKUP Exact Match Example Worksheet

Excel XLOOKUP Exact Match Example Formula

Excel XLOOKUP vs. VLOOKUP Exact Match Example Formulas

Excel XLOOKUP vs. INDEX MATCH Exact Match Example Formulas

(2) Excel XLOOKUP Approximate Match

Excel XLOOKUP Approximate Match Formula Template/Structure

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

Additional Cues for Excel XLOOKUP Approximate Match

Excel XLOOKUP Approximate Match Example Worksheet

Excel XLOOKUP Approximate Match Example Formula

Excel XLOOKUP vs. VLOOKUP Approximate Match Example Formulas

Excel XLOOKUP vs. INDEX MATCH Approximate Match Example Formulas

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

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

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

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

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

Excel XLOOKUP Return Multiple Values (Columns) Example Formula

Excel XLOOKUP vs. VLOOKUP Return Multiple Values (Columns)

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel XLOOKUP Multiple Criteria (with Boolean Logic) Example Worksheet

Excel XLOOKUP Multiple Criteria (with Boolean Logic) Example Formula

Excel XLOOKUP Multiple Criteria (with Boolean Logic) vs. VLOOKUP Multiple Criteria Example Formulas

Excel XLOOKUP Multiple Criteria (with Boolean Logic) vs. INDEX MATCH Multiple Criteria Example Formulas

(6) Excel XLOOKUP Two-Way Lookup

Excel XLOOKUP Two-Way Lookup Formula Template/Structure

Step-by-Step Process to Set Up an Excel XLOOKUP Two-Way Lookup Formula

Additional Cues for Excel XLOOKUP Two-Way Lookup

Excel XLOOKUP Two-Way Lookup Example Worksheet

Excel XLOOKUP Two-Way Lookup Example Formula

Excel XLOOKUP vs. VLOOKUP Two-Way Lookup Example Formulas

Excel XLOOKUP vs. INDEX MATCH Two-Way Lookup Example Formulas

(7) Excel XLOOKUP Multiple Criteria Column and Row

Excel XLOOKUP Multiple Criteria Column and Row Formula Template/Structure

Step-by-Step Process to Set Up an Excel XLOOKUP Multiple Criteria Column and Row Formula

Additional Cues for Excel XLOOKUP Multiple Criteria Column and Row

Excel XLOOKUP Multiple Criteria Column and Row Example Worksheet

Excel XLOOKUP Multiple Criteria Column and Row Example Formula

Excel XLOOKUP vs. VLOOKUP Multiple Criteria Column and Row Example Formulas

Excel XLOOKUP vs. INDEX MATCH Multiple Criteria Column and Row Example Formulas

Download the Excel XLOOKUP Example Workbook

Related Excel Training Materials and Resources