How to Return a Table Value Based on Multiple Criteria in Excel
Question details
The user needs to extract a specific value from a data table based on three dynamic selections: year, session, and a specific data point (column header).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a dynamic dashboard or report where users select criteria from dropdowns to view specific metrics like pass rates or averages.
- Observed behavior
- The goal is to dynamically return the intersecting value of a row matching two conditions (Year and Session) and a column matching a third condition (Data Point).
Ensure your data is formatted as an official Excel Table (e.g., 'Table1') and that your lookup values exactly match the text in the table's columns and headers.
Use Nested XLOOKUP for Multiple Criteria
The most modern and efficient way to perform a two-condition row lookup and a dynamic column lookup.
This method uses boolean logic to match multiple row criteria and nests a second XLOOKUP to dynamically find the correct column based on the header name.
Click on the cell where you want the retrieved value to appear.
Type the formula: =XLOOKUP(1,(Table1[Year]=A2)*(Table1[Session]=B2),XLOOKUP(C2,Table1[#Headers],Table1)) where A2 is the target year, B2 is the target session, and C2 is the target column header.
Press Enter. The inner XLOOKUP finds the correct column, and the outer XLOOKUP finds the intersecting row.
Use INDEX and MATCH for Older Excel Versions
A robust alternative that works in all versions of Excel, including those without XLOOKUP.
Use the FILTER Function
A clean approach using dynamic arrays to filter rows and then columns.
Use Advanced Lookup Functions in WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like XLOOKUP and FILTER, making it incredibly easy to build dynamic reports and dashboards without complex workarounds.
- 1. Open your workbook: Launch WPS Spreadsheet and open your dataset.
- 2. Format as Table: Select your data range and press Ctrl+T to convert it into a structured table.
- 3. Apply dynamic formulas: Use the =XLOOKUP() or =FILTER() functions exactly as you would in Excel to retrieve your dynamic values based on multiple criteria.
Frequently Asked Questions
Why does my XLOOKUP formula return an #N/A error?
This usually happens if the lookup values (like the text in C2) do not exactly match the table headers, or if there is no row that matches both the selected Year and Session. Check for trailing spaces or spelling typos in your cells.
Can I use dropdown lists for the Year, Session, and Selection cells?
Yes. Go to Data > Data Validation, choose 'List', and select the range of your years, sessions, or headers. This prevents typing errors and makes your lookup formula fully interactive.
What if there are multiple rows with the same Year and Session?
XLOOKUP and INDEX/MATCH will return the first matching record they find from top to bottom. If you need to sum or average multiple matching records, use the SUMIFS or AVERAGEIFS functions instead.
Do these formulas work if my data is not formatted as a Table?
Yes, but you will need to replace the structured references (like Table1[Year]) with standard absolute ranges (like $A$2:$A$100). Using official Tables is highly recommended because they automatically expand when new data is added, keeping your formulas intact.
