• New? Start here
  • Excel and VBA Training Resources
    • Premium Courses
    • Books
    • Cheat Sheets
    • Blog Archives
  • VBA Code Generator
  • Academy Login

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

By J.A. Gomez J.A. Gomez from Power Spreadsheets

Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)In this Excel VLookup Multiple Criteria (with XLOOKUP) Tutorial, you learn how to carry out a VLookup for multiple criteria with the XLOOKUP function.

The VLookup multiple criteria (with XLOOKUP) formula template/structure you learn in this Tutorial isn't the only way to carry out a VLookup for multiple criteria. You may (also) be interested in the following alternative formula template/structures:

  • Excel VLookup Multiple Criteria with INDEX MATCH.
  • Excel VLookup Multiple Criteria with the FILTER Function.

I link to these Tutorials in the Related Excel Training Materials and Resources Section below.

This Excel VLookup Multiple Criteria (with XLOOKUP) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

Table of Contents

  • Related Excel Training Materials and Resources
  • The VLookup Multiple Criteria (with XLOOKUP) Formula Template/Structure
  • The Example Before VLookup Multiple Criteria (with XLOOKUP)
  • Step 1: Set Up the Multiple Conditions
    • Step 1 Example
  • Step 2: Use the XLOOKUP Function to Extract the Value in the Row Where the Multiple Conditions are Met
    • Step 2 Example
  • Download the VLookup Multiple Criteria (with XLOOKUP) Example Workbook
  • Related Excel Training Materials and Resources

Related Excel Training Materials and Resources

This Excel VLookup Multiple Criteria (with XLOOKUP) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

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

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents below.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

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

  • Excel Macro Tutorial for Beginners: Click here to open.
  • Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
  • 350 + Excel Keyboard Shortcuts And Hotkeys: 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). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning 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 consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

The VLookup Multiple Criteria (with XLOOKUP) Formula Template/Structure

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,(Criterion1[ComparisonOperator]ColumnWithCriterion1)*(Criterion2[ComparisonOperator]ColumnWithCriterion2)*(Criterion3[ComparisonOperator]ColumnWithCriterion3)*…*(Criterion#[ComparisonOperator]ColumnWithCriterion#),ColumnWithValueToReturn)

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

The Example Before VLookup Multiple Criteria (with XLOOKUP)

This Excel VLookup Multiple Criteria (with XLOOKUP) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

The example worksheet has 2 tables with the following characteristics.

(1) Table 1 (Cells A7 to G27).

The table:

  • With the data.
  • Where you search with the VLookup multiple criteria (with XLOOKUP) example formula.

Main characteristics:

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

(2) Table 2 (Cells I7 to L14).

The table where you:

  • Set up the VLookup multiple criteria (with XLOOKUP) example formulas.
  • Display the results.

This table has the following 4 columns:

  • Column: Cells I8 to I14 store letters. These letters match the columns of Table 1.
  • Header: The values in cells J8 to J14 match the headers of Table 1 (cells A7 to G7).
  • Value:
    • Cells K8 and K9: The 2 (multiple) VLookup criteria.
    • Cells K10 to K14:
      • Currently empty.
      • Will store the VLookup multiple criteria (with XLOOKUP) example formulas.
  • Formula: Cells L10 to L14:
    • Currently display the #N/A error.
    • Will display the VLookup multiple criteria (with XLOOKUP) example formulas I enter in cells K10 to K14.

The following image displays the example worksheet before I add the VLookup multiple criteria (with XLOOKUP) example formulas.

Example: Data for VLookup multiple criteria (with XLOOKUP) formula

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

Step 1: Set Up the Multiple Conditions

Create an expression that does the following:

  1. Evaluate multiple conditions.
  2. Return a single value for each row (in the table where you search with the VLookup multiple criteria formula), indicating whether all conditions are met (or not) in that specific row.

To do this inside a VLookup multiple criteria (with XLOOKUP) formula, you work with logical expressions. Logical expressions

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

Use the following template/structure to create the expression testing the multiple conditions:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
(Criterion1[ComparisonOperator]ColumnWithCriterion1)*(Criterion2[ComparisonOperator]ColumnWithCriterion2)*(Criterion3[ComparisonOperator]ColumnWithCriterion3)*…*(Criterion#[ComparisonOperator]ColumnWithCriterion#)

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

  • A comparison operator.
    • Comparison operators:
      • Carry out comparisons.
      • 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:
      • TRUE is converted to 1.
      • FALSE is converted to 0.
    • When creating a VLookup multiple criteria (with XLOOKUP) formula, the multiplication operator (*) acts as the AND logical operator. In other words: The entire expression returns 1 (TRUE) or 0 (FALSE) as follows:
      • 1 (TRUE) if all evaluated conditions return 1 (TRUE).
      • 0 (FALSE) if any (or all) evaluated condition(s) return 0 (FALSE).

Consider the following when specifying the expression testing the multiple conditions:

  • 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 (with the respective criterion).
  • Specify the column with a criterion (ColumnWithCriterion#) as a single-column (1 column, several rows) cell range.

In the VLookup multiple criteria (with XLOOKUP) formula, the expression template/structure I describe above returns an array of 0s and 1s, by applying the 2 operators above in the following order of precedence:

  • First:
    • The comparison operator is evaluated.
    • Each expression with a comparison operator returns an array with TRUEs and FALSEs. Each individual TRUE or FALSE indicates whether the applicable criterion (Criterion#) is met (or not) by the applicable row in the applicable column (ColumnWithCriterion#).
  • Second:
    • The multiplication operator (*) is evaluated.
    • The entire expression with the multiplication operator(s) (*) returns an array with 1s (TRUE) and 0s (FALSE). Each individual 1 (TRUE) or 0 (FALSE) indicates whether all applicable criteria are met by all applicable columns in the applicable row.

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

Step 1 Example

In the example, 2 conditions must be met:

  • Condition 1: The Product (in column D) is equal to the product in cell K8.
  • Condition 2: The City (in column E) is equal to the city in cell K9.

When implementing the template/structure I describe above:

  • Criterion1: Cell K8 ($K$8, when using absolute references).
  • ColumnWithCriterion1: Cells D8 to D27 ($D$8:$D$27, when using absolute references).
  • Criterion2: Cell K9 ($K$9, when using absolute references).
  • ColumnWithCriterion2: Cells E8 to E27 ($E$8:$E$27, when using absolute references).
  • Comparison operator (for both conditional tests): Equal to (=).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27)

The image below illustrates how the array of 0s and 1s (I mention above) is returned by this expression:

  1. Columns H (Criterion1) and I (Criterion2) return TRUE or FALSE, depending on whether the criterion (Criterion#) is met by the applicable row in the applicable column (column D for Criterion1, column E for Criterion2).
  2. Column J (Criterion1*Criterion2) returns 0 or 1, depending on whether all criteria (Criterion1 and Criterion2) are met by all applicable columns (column D and column E) in the applicable row.
  3. Column K (Step 1 Array):
    • Uses the basic structure of the expression testing the multiple conditions (I describe above).
    • Returns 0 or 1, depending on whether all criteria (Criterion1 and Criterion2) are met by all applicable columns (column D and column E). These results are exactly the same as those returned by the (illustrative) process carried out in columns H, I, and J.
  4. Cell L8 displays the formula (using the basic structure of the expression testing the multiple conditions) stored in column K.
Example: Set Up the Multiple Conditions for Excel VLookup multiple criteria (with XLOOKUP)

The 2 (multiple) conditions are met in row 24 of the worksheet.

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

Step 2: Use the XLOOKUP Function to Extract the Value in the Row Where the Multiple Conditions are Met

In the VLookup multiple criteria (with XLOOKUP) formula, the XLOOKUP function does the following:

  1. Search for an item in an array; and
  2. Return the corresponding item from a single-column (1 column, several rows) cell range.

Specify XLOOKUP's arguments as follows to obtain the value the VLookup multiple criteria (with XLOOKUP) formula returns:

  1. lookup_value: 1.
  2. lookup_array: The expression (returning an array of 0s and 1s) you created in step #1.
  3. return_array: The single-column (1 column, several rows) cell range with the value to return.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,ArrayFromStep1,ColumnWithValueToReturn)

When considering the array you created in step #1:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,(Criterion1[ComparisonOperator]ColumnWithCriterion1)*(Criterion2[ComparisonOperator]ColumnWithCriterion2)*(Criterion3[ComparisonOperator]ColumnWithCriterion3)*…*(Criterion#[ComparisonOperator]ColumnWithCriterion#),ColumnWithValueToReturn)

Consider the following when setting up the XLOOKUP function for the VLookup multiple criteria (with XLOOKUP) formula:

  • The XLOOKUP function is available in Excel 2021 and later (including Excel 365).
  • By default, XLOOKUP:
    • Searches for an exact match of the lookup_value (1).
    • Works with the first entry matching the lookup_value (1).
    • Returns the #N/A error if no exact match of the lookup_value (1) is found.

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

Step 2 Example

The VLookup multiple criteria (with XLOOKUP) example formulas (using absolute references) are as follows:

  • Cell K10:
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),A$8:A$27)
  • Cell K11:
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),B$8:B$27)
  • Cell K12:
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),C$8:C$27)
  • Cell K13:
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),F$8:F$27)
  • Cell K14:
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-criteria-xlookup/
=XLOOKUP(1,($K$8=$D$8:$D$27)*($K$9=$E$8:$E$27),G$8:G$27)

The formulas above differ (exclusively) on the column specified as return_array argument of the XLOOKUP function. That's the column (in the table where you search with the VLookup multiple criteria formula) with the value to return.

  • Cell K10: Returns a value from column A (Salesperson).
  • Cell K11: Returns a value from column B (Date).
  • Cell K12: Returns a value from column C (Customer).
  • Cell K13: Returns a value from column F (Units).
  • Cell K14: Returns a value from column G (Unit Price).

The image below displays the final results returned by the VLookup multiple criteria (with XLOOKUP) example formulas.

  • Cells K10 to K14 display the value returned by the applicable VLookup multiple criteria (with XLOOKUP) example formula, as follows:
    • Cell K10: The value in cell A24 (Salesperson 50).
    • Cell K11: The value in cell B24 (22-May-19).
    • Cell K12: The value in cell C24 (Customer 5).
    • Cell K13: The value in cell F24 (84.00).
    • Cell K14: The value in cell G24 (499.99).
  • Cells L10 to L14 display the VLookup multiple criteria (with XLOOKUP) example formulas, as used in cells K10 to K14.
Example: XLOOKUP function for Excel VLookup multiple criteria (with XLOOKUP)

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

Download the VLookup Multiple Criteria (with XLOOKUP) Example Workbook

This Excel VLookup Multiple Criteria (with XLOOKUP) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.

Get immediate free access to the Excel VLookup multiple criteria with XLOOKUP workbook example

Related Excel Training Materials and Resources

This Excel VLookup Multiple Criteria (with XLOOKUP) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

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

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents above.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

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

  • Excel Macro Tutorial for Beginners: Click here to open.
  • Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
  • 350 + Excel Keyboard Shortcuts And Hotkeys: 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). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning 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 consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

guest
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
About

Contact

Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure

Copyright © 2015–2022 PDS Intelligence Pte. Ltd. All rights reserved.
Excel ® is a registered trademark of the Microsoft Corporation. Power Spreadsheets is not affiliated with the Microsoft Corporation.