How To XLOOKUP With Multiple Criteria
The XLOOKUP function is one of the most versatile tools in modern Excel, replacing older functions like VLOOKUP and HLOOKUP. While a standard XLOOKUP searches for a single value in a range, many real-world datasets require you to match multiple conditions—such as finding a specific "Employee Name" within a specific "Department."
To unlock the true power of the XLOOKUP function, you must learn how to handle multi-criteria searches. Since XLOOKUP is designed to look for one value, the secret lies in using Boolean logic to combine multiple criteria into a single search array.
Understanding the Logic of Multiple Criteria
In a standard XLOOKUP, you provide a lookup value and a lookup array. To search for multiple criteria, you cannot simply list several values in the first argument. Instead, you create a "virtual array" using the number 1 as your lookup value. This tells Excel to look for a row where all your specified conditions are TRUE.
In Excel, TRUE is treated as 1 and FALSE is treated as 0. By multiplying multiple criteria together, Excel performs a logical AND operation. If all conditions are met (1 * 1 * 1), the result is 1, and XLOOKUP returns the corresponding value.
Step-by-Step Guide: How to Use XLOOKUP with Multiple Criteria
Imagine you have a table with three columns: First Name, Last Name, and Salary. You want to find the salary of a person named "John Doe."
The Formula Syntax:
=XLOOKUP(1, (CriteriaRange1=Criteria1) * (CriteriaRange2=Criteria2), ReturnRange)
- The Lookup Value: Set this to 1. This represents the "TRUE" state you are searching for.
- The Lookup Array: This is where the magic happens. You wrap each condition in parentheses and multiply them. For example: (A2:A10="John") * (B2:B10="Doe").
- The Return Array: This is the column containing the data you want to retrieve (e.g., the Salary column C2:C10).
Example Scenario
If your data is organized as follows:
- Column A: First Name
- Column B: Last Name
- Column C: Salary
To find the salary for John Doe, your formula would look like this:
=XLOOKUP(1, (A2:A10="John") * (B2:B10="Doe"), C2:C10)
How it works internally:
- Excel checks Column A for "John" and creates an array of TRUE/FALSE.
- Excel checks Column B for "Doe" and creates another array of TRUE/FALSE.
- It multiplies these arrays. Only the row where both are TRUE (1 * 1) results in a 1.
- XLOOKUP finds that 1 and returns the value from Column C.
Advanced Tips for Dynamic Lookups
To learn dynamic data management, avoid hard-coding names like "John" or "Doe" directly into your formula. Instead, reference cells. This allows you to change the search criteria without editing the formula itself.
Dynamic Formula:
=XLOOKUP(1, (A2:A10=E2) * (B2:B10=F2), C2:C10)
In this version, E
