How to Use INDEX MATCH in Google Sheets
INDEX MATCH in Google Sheets is a flexible lookup pattern that handles left lookups and survives inserted columns. Here's the syntax, examples, and when to use it.
Reviewed by Griddy
Updated for current Excel and Google Sheets workflows, with examples chosen to map back to real spreadsheet tasks rather than abstract formula syntax.
INDEX MATCH in Google Sheets is a lookup pattern built from two functions:
MATCHfinds the position of a valueINDEXreturns the value at that position
Together, they solve some of the biggest VLOOKUP problems, especially when the return column is to the left or the table structure changes often.
The syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))- return_range — the cells you want to return from
- lookup_value — the value you want to find
- lookup_range — the column or row to search
- 0 — exact match
The 0 matters. Without it, MATCH may use approximate matching, which is usually wrong for business data.
Basic example
Say column A contains company names, column B contains owner, and column C contains stage. If cell E2 contains the company name and you want the owner:
=INDEX(B2:B100, MATCH(E2, A2:A100, 0))That formula:
- looks for the company in
A2:A100 - finds the matching row number
- returns the owner from
B2:B100
To return stage instead, switch the return range:
=INDEX(C2:C100, MATCH(E2, A2:A100, 0))Why use INDEX MATCH instead of VLOOKUP
VLOOKUP would need the lookup column to be the first column in the selected range. INDEX MATCH does not care.
That makes it a strong fit for sheets where:
- the return column sits to the left
- columns may be inserted later
- the sheet structure is likely to change
This is common in a CRM lead tracker or a sales pipeline template where new fields get added over time.
A two-way lookup pattern
You can also combine INDEX with MATCH twice to pull from a table where both the row and the column are dynamic:
=INDEX(B2:E10, MATCH(H2, A2:A10, 0), MATCH(I2, B1:E1, 0))This pattern is useful when:
H2contains the row label you wantI2contains the column label you want
It works well for scorecards, budgets, or planning tables where both dimensions may change.
✦ TIP
If you only need a simple one-direction lookup and XLOOKUP is available, XLOOKUP is usually easier to read. INDEX MATCH still matters when you need more control over structure.
Common problems
| Error | Cause | Fix |
|---|---|---|
#N/A | MATCH found no exact value | Check for extra spaces, text/number mismatch, or wrong lookup range |
| Wrong result | Lookup and return ranges are not aligned | Make sure both ranges start and end on the same rows |
| Formula breaks after copy | Relative references shift | Lock stable ranges with $ if needed |
The Griddy way
INDEX MATCH is powerful, but it is easy to point at mismatched ranges or write a formula that is harder to audit later.
"Match each company in this follow-up tab to the owner column on the Leads tab and return blank if there is no match"
Griddy can choose the right lookup pattern, write the formula against the real ranges in your workbook, and fix the fragile parts before they break.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Use more resilient lookups in Google Sheets
INDEX MATCH is useful when live operating sheets need left lookups or a safer pattern than fragile column-index based formulas.
CRM Lead Tracker
Track contacts, lead source, owner, next due date, and follow-up status in one lightweight CRM sheet. Keep hot opportunities and stale leads visible without paying for heavy sales software.
Sales Pipeline
Track deals by stage, owner, value, and next move in one lightweight pipeline sheet. Keep close dates, weighted forecast, and rep follow-ups visible without needing a full CRM.
CRM Lead Tracker for Consultants
Track consulting leads, discovery calls, proposal status, retainers, and follow-up dates in one free CRM spreadsheet built for consultant workflows.
CRM Lead Tracker for Real Estate
Track real estate leads, property interest, follow-up dates, pipeline stages, and agent ownership in one free CRM spreadsheet template.