How to Return a Value in Excel Based on Multiple Criteria?
When working with large datasets, you often need to return a value that depends on multiple criteria. For example, you may want to match both a product name and region or find a price based on a category and size. Excel provides strong lookup functions like VLOOKUP, INDEX MATCH, and XLOOKUP to help you return correct results.
In this guide, we will explain how to return values using multiple conditions using simple examples and formulas. You will also learn how to use helper columns and structured tables to improve accuracy.
Using INDEX and MATCH for Multiple Criteria
The INDEX MATCH combination is flexible and works with both vertical and horizontal lookups. It also avoids limitations of VLOOKUP such as fixed column order.
Example Data
| Product | Region | Price |
|---|---|---|
| Shoes | East | 50 |
| Shoes | West | 55 |
| Jacket | East | 90 |
| Jacket | West | 95 |
Goal: Return the price of Shoes sold in the West region.
Formula
=INDEX(C2:C5, MATCH(1, (A2:A5="Shoes")*(B2:B5="West"), 0))How it Works
- The MATCH part creates a series of TRUE/FALSE conditions.
- TRUE becomes 1, FALSE becomes 0.
- Only the row where both conditions are TRUE equals 1.
- INDEX returns the Price from that matching row.
Important Notes
- This is an array formula. In older Excel versions, confirm using Ctrl + Shift + Enter.
- In Excel 365 or Excel 2021, just press Enter.
Keywords used: Multiple criteria lookup, INDEX function, MATCH function, conditional lookup.
Using XLOOKUP with Multiple Criteria
XLOOKUP is the most modern lookup function in Excel. It allows exact, approximate, wildcard, and multiple condition lookups.
Formula
=XLOOKUP(1, (A2:A5="Shoes")*(B2:B5="West"), C2:C5)Benefits of XLOOKUP
- Can lookup left or right (no column limitations)
- No need for helper columns
- Supports dynamic ranges and Excel Tables
Using a Helper Column to Combine Criteria
Helper columns simplify complex formulas, especially for beginners.
- Create a helper column that joins fields.
- Use a simple lookup function like VLOOKUP.
Example Data with Helper Column
| Product | Region | Combined Key | Price |
|---|---|---|---|
| Shoes | East | Shoes-East | 50 |
| Shoes | West | Shoes-West | 55 |
| Jacket | East | Jacket-East | 90 |
| Jacket | West | Jacket-West | 95 |
Formula to Create the Key
=A2 & "-" & B2VLOOKUP Formula
=VLOOKUP("Shoes-West", C2:D5, 2, FALSE)Why Use This Method
- Best for quick lookups
- Easy for users not comfortable with array formulas
Returning Multiple Values Based on Multiple Criteria
Sometimes, you need more than one result, not just the first match. Excel 365 allows spill formulas that return an array.
Example
Return all prices of “Shoes”.
=FILTER(C2:C5, (A2:A5="Shoes"))Combining Multiple Conditions
=FILTER(C2:C5, (A2:A5="Shoes")*(B2:B5="West"))This returns all matching rows dynamically.
Using SUMIFS, COUNTIFS or AVERAGEIFS for Numeric Results
If your return value is a number and you want aggregation (sum, count, or average), you can use IFS lookup functions.
Example
Total sales for Shoes in East region:
=SUMIFS(C2:C5, A2:A5, "Shoes", B2:B5, "East")Related Functions
| Function | Purpose |
|---|---|
| SUMIFS | Add values matching multiple criteria |
| COUNTIFS | Count values matching multiple criteria |
| AVERAGEIFS | Average values matching multiple criteria |
These are great for quick data analysis dashboards.
Using Structured Excel Tables for Easier Lookup
Convert your data to an Excel Table to get dynamic and easy-to-read formulas.
Steps
- Select data range
- Press Ctrl + T
- Name the table (e.g., SalesData)
Example Formula Using Table References
=INDEX(SalesData[Price], MATCH(1, (SalesData[Product]="Shoes")*(SalesData[Region]="West"), 0))Table names make formulas easier to maintain when data grows.
Advanced Technique: CHOOSE with VLOOKUP
You can trick VLOOKUP into using multiple criteria by combining columns using CHOOSE.
Formula
=VLOOKUP("ShoesWest", CHOOSE({1,2}, A2:A5&B2:B5, C2:C5), 2, FALSE)Why It Helps
- Works even if lookup column is not first
- No need for helper column in the sheet
Common Errors and How to Fix Them
| Error | Cause | Fix |
|---|---|---|
| #N/A | Criteria not found | Check spelling or use IFERROR |
| #VALUE! | Wrong array handling | Press Ctrl + Shift + Enter (older Excel) |
| Wrong result | Criteria range mismatch | Ensure all ranges are same size |
Use IFERROR for Cleaner Output
=IFERROR(INDEX(...), "Not found")This helps create user-friendly dashboards.
Best Practices for Multiple Criteria Lookups
To improve accuracy and performance:
- Use Excel Tables to keep formulas dynamic
- Avoid merging cells in lookup ranges
- Always use exact match arguments (FALSE or 0)
- Clean data with TRIM, CLEAN, or DATA VALIDATION
- Avoid extra spaces by combining LSI keywords like lookup value, match values, returned column
Data Cleaning Example
=TRIM(A2)Clean data reduces duplicate issue and mismatched lookups.
Choosing the Best Method
| Requirement | Best Formula |
|---|---|
| Most flexible lookup | INDEX MATCH |
| Easiest and modern | XLOOKUP |
| Quick for beginners | Helper Column + VLOOKUP |
| Return multiple rows | FILTER |
| Add, count, or average values | SUMIFS / COUNTIFS / AVERAGEIFS |
Final Thoughts
Returning a value with multiple criteria in Excel is essential for accurate data analysis. Whether you use INDEX MATCH, XLOOKUP, or SUMIFS, the goal is to combine conditions and return the correct data every time. Once you understand these formulas, you can build reports, financial models, and dashboards that handle large and complex data smoothly.
Start with the method you feel most comfortable with and improve as you practice. The more you use these Excel lookup functions, the easier data management becomes.
Frequently Asked Questions
What formula can I use to return a value in Excel based on multiple criteria?
The most common formula to return a value based on multiple criteria in Excel is
INDEX MATCH with a logical test. A typical pattern is:
=INDEX(return_range, MATCH(1, (criteria_range1=criteria1)*(criteria_range2=criteria2), 0))
This setup checks each condition, multiplies them, and returns the row where all conditions are true.
What is the difference between INDEX MATCH and XLOOKUP for multiple criteria?
Both INDEX MATCH and XLOOKUP can handle multiple criteria, but XLOOKUP is simpler in
modern Excel versions. With XLOOKUP, you can use:
=XLOOKUP(1, (range1=criteria1)*(range2=criteria2), return_range)
INDEX MATCH works in more Excel versions, while XLOOKUP is easier to read and does not require
Ctrl + Shift + Enter in newer Excel versions.
How do I avoid #N/A errors with multiple criteria lookup formulas?
To avoid #N/A errors, wrap your lookup formula with IFERROR. For example:
=IFERROR(INDEX(return_range, MATCH(1, (range1=criteria1)*(range2=criteria2), 0)), "Not found")
Also make sure your ranges are the same size, criteria are spelled correctly, and there are no extra spaces in
the data.
Can I use VLOOKUP with multiple criteria in Excel?
Yes, you can use VLOOKUP with multiple criteria by creating a helper column that
combines fields. For example, join Product and Region with:
=A2 & "-" & B2
Then use VLOOKUP on this combined key. Another method is to use CHOOSE inside VLOOKUP, but that is
more advanced.
How can I return multiple results based on multiple criteria in Excel?
If you use Microsoft 365 or Excel 2021, you can return multiple results with the
FILTER function. For example:
=FILTER(result_range, (range1=criteria1)*(range2=criteria2))
This formula returns all matching rows that meet your multiple conditions and spills them into nearby cells.
Which is the best method to use for multiple criteria lookups in Excel?
The best method depends on your Excel version and comfort level:
- XLOOKUP – best for modern Excel, simple and flexible.
- INDEX MATCH – very powerful and works in most Excel versions.
- Helper column + VLOOKUP – good for beginners and quick solutions.
- FILTER – best when you need to return multiple rows at once.
Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.