How to Use INDEX and MATCH
Last updated: 2026-09-06
⚡ Quick Answer
The combination of INDEX and MATCH allows you to look up values across horizontal and vertical axes safely.
=INDEX(C2:C100, MATCH(E1, A2:A100, 0))📊 Visual Demo
HOME · INSERT · FORMULAS · DATA
fx =INDEX(C2:C100, MATCH(E1, A2:A100, 0))
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1 | Widget A | Hardware | $25 | =INDEX(D:D, MATCH("Widget B", B:B, 0)) |
| 2 | 2 | Widget B | Software | $45 | - |
| 3 | 3 | Query Target | Widget B | Match Found | $45 Price Tag |
🧠 Deep Dive
The combination of INDEX and MATCH allows you to look up values across horizontal and vertical axes safely.
Key Insights
- XLOOKUP searches any direction and defaults to exact match.
- Boolean multiplication enables multi-criteria lookups.
Common Mistakes
- Forgetting exact match argument in VLOOKUP.
- Lookup column containing hidden spaces.
Pro Tips
- Always wrap lookups in IFERROR.
- Clean keys with TRIM.
FAQ
What does this formula do in plain English?
The combination of INDEX and MATCH allows you to look up values across horizontal and vertical axes safely.
Why is my formula not working?
Forgetting exact match argument in VLOOKUP.
Does this work in Google Sheets?
Yes, this syntax is fully compatible with Google Sheets.
What is the most common mistake?
Lookup column containing hidden spaces.
Related Guides
How to Use XLOOKUP with Multiple CriteriaHow to VLOOKUP to the LeftHow to Use the XMATCH FunctionExcel Error Hub
Need Complete Playbooks?
Download our verified manuals covering ERP Cleanup and Modeling.
Explore Playbook Series (From ₹199)