How to Perform an Excel Multi-Criteria Lookup Returning Multiple Values
Question details
The user needs a formula-based method to perform a lookup in Excel using multiple criteria that can return a range of values, preferring dynamic formulas over Power Query for smaller datasets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching through a dataset of varying sizes to find and extract data that simultaneously matches two or more specific conditions.
- Observed behavior
- The goal is to retrieve either the first matching result based on multiple conditions or to extract all rows and values that meet those conditions without using complex macros.
Ensure that you are using a recent version of Excel or WPS Spreadsheet that supports dynamic array functions like XLOOKUP and FILTER, as older versions may require complex array formulas or helper columns.
Use the FILTER Function to Return Multiple Matches
The FILTER function is the ideal solution when you need to extract all rows or values that match multiple criteria simultaneously.
By multiplying Boolean arrays (e.g., (Range1=Criteria1)*(Range2=Criteria2)), you create an AND logic test. The FILTER function processes this logic and returns all rows where the combined condition is true. The results will automatically spill into the adjacent cells.
Click on the empty cell where you want the first result to appear. Ensure there is enough empty space below and to the right for the results to spill into.
Type the formula following this structure: =FILTER(Return_Range, (Criteria_Range1=Condition1)*(Criteria_Range2=Condition2), "Not found").
For example, type =FILTER(C2:C100, (A2:A100=G2)*(B2:B100=H2), "") and press Enter. Excel will instantly populate the matching records that meet all your specified criteria.
Use XLOOKUP with Boolean Logic for a Single Match
If you only need to return the very first matching value based on multiple criteria, XLOOKUP is highly efficient and easy to write.
Use a Helper Column for Older Versions of Excel
If your spreadsheet software does not support XLOOKUP or FILTER, you can combine your criteria into a single helper column to use standard lookup functions.
Master Advanced Data Analysis with WPS Office
WPS Spreadsheet fully supports modern dynamic array functions like XLOOKUP and FILTER, allowing you to easily handle complex multi-criteria lookups and retrieve multiple values without writing complicated macros.
- 1. Open Your Data: Launch WPS Spreadsheet and open your workbook containing the data tables.
- 2. Select the Output Area: Click on the cell where you want the multi-criteria results to be displayed.
- 3. Input the Formula: Type your =FILTER() or =XLOOKUP() formula utilizing the Boolean multiplier logic for your multiple criteria.
- 4. Execute the Formula: Press Enter. WPS Spreadsheet will instantly calculate the logic and spill the resulting multiple values into the grid.
Frequently Asked Questions
Why is my XLOOKUP returning an error when using multiple criteria?
This usually happens if the criteria ranges are not exactly the same size. Ensure that all ranges inside your formula (e.g., A2:A100 and B2:B100) have the exact same number of rows. Also, verify that you are using 1 as the primary lookup value.
Can I use OR logic instead of AND logic in the FILTER function?
Yes. To evaluate multiple criteria using OR logic (where matching ANY condition returns the row), combine the arrays using a plus sign (+) instead of an asterisk (*). For example: =FILTER(C2:C100, (A2:A100=G2)+(B2:B100=H2)).
What happens if the FILTER function finds no matches?
By default, the FILTER function returns a #CALC! error if no records match your criteria. You can handle this gracefully by providing a text string in the third argument, such as =FILTER(range, criteria, "No matches found").
