Advanced Excel INDEX/MATCH: Two-Way Lookup, Partial Matches, Multiple Criteria, and Last Values
Use Excel INDEX MATCH to overcome the limitations of VLOOKUP/HLOOKUP. This guide covers two-way (row and column) lookups, partial matches, case sensitivity, multiple criteria, and last values with copy-and-paste-ready formulas and tables. If you use VLOOKUP, also see the VLOOKUP Error Fix Guide and Stabilize Your Lookups with XLOOKUP Basics.
Quick Fix
=INDEX(PriceList[Price], MATCH([@Item], PriceList[Item], 0))=INDEX(MonthlySales, MATCH(B2, MonthlySales[Item], 0), MATCH(C2, MonthlySales[#Headers], 0))=INDEX(Sales[Price], MATCH(1, (Sales[Item]=E2)*(Sales[Date]>=G2)*(Sales[Date]<=H2), 0))=LOOKUP(2, 1/(Sales[Item]=E2), Sales[Price])Sample Data
| Item | Name | Price | Tier |
|---|---|---|---|
| T001 | T-Shirt | 19000 | Basic |
| T002 | Polo | 25000 | Plus |
| B014 | Pants | 35000 | Basic |
| O010 | Jacket | 159000 | Pro |
| S201 | Shoes | 140000 | Pro |
Two-Way (Row and Column) Lookup
=INDEX(MonthlySales,
MATCH(B2, MonthlySales[Item], 0),
MATCH(C2, MonthlySales[#Headers], 0))Partial Matches and Case Sensitivity
=INDEX(PriceList[Price], MATCH("*Shirt*", PriceList[Name], 0))=INDEX(PriceList[Price], MATCH(TRUE, EXACT(PriceList[Item], E2), 0))Multiple Criteria (Date Range + Item)
For totals or counts with multiple criteria, a combination of functions may be more convenient. In these cases, using SUMIF and COUNTIF for Conditional Aggregation can be efficient.
=INDEX(Sales[Price], MATCH(1, (Sales[Item]=E2)*(Sales[Date]>=G2)*(Sales[Date]<=H2), 0))Last Value (Most Recent Item)
=LOOKUP(2, 1/(Sales[Item]=E2), Sales[Price])Checklist & Troubleshooting
- Use tables, set MATCH to exact match (0), and ensure ranges are the same size.
- Approximate matches require sorting; use the LOOKUP pattern for the last value.
| Symptom | Cause | Solution |
|---|---|---|
| #N/A | Spaces/formatting | Use TRIM/CLEAN and standardize formatting |
| Incorrect value | Approximate-match sorting requirement not met | Sort the data or use an exact match |
| Only the first value appears | Missing parentheses | Wrap the multiplied criteria in parentheses |
| Case sensitivity needed | Not case-sensitive by default | EXACT+MATCH(TRUE,…,0) |
| Most recent value needed | MATCH returns the first match | LOOKUP(2,1/(criteria),range) |
Related Articles
- Stabilize Your Lookups with XLOOKUP Basics
- VLOOKUP Error Fix Guide
- SUMIF and COUNTIF for Conditional Aggregation
Conclusion
With INDEX/MATCH, you can reliably perform two-way lookups, partial matches, multiple-criteria lookups, and retrieve last values. Continue with the related articles above to build a smoother workflow for real-world tasks.