Excel formula builder
INDEX MATCH Formula Builder
Generate an exact-match INDEX MATCH formula for Excel or Google Sheets. Use it when the return column can be left or right of the lookup column.
Builder inputs
Use one lookup column and one return column with the same row height.
=INDEX(Products!D2:D100, MATCH(A2, Products!A2:A100, 0))=INDEX(Products!D2:D100, MATCH(A2, Products!A2:A100, 0))INDEX MATCH works in both Excel and Google Sheets and does not require the return column to sit to the right.How this formula works
- MATCH finds the row position of the lookup value inside the lookup range.
- INDEX returns the value at that same row position from the return range.
- The final 0 in MATCH means exact match.
- Because the lookup range and return range are separate, the return column can be either left or right of the lookup column.
Best fit
Best for
- Exact-match lookups where VLOOKUP would need a fragile column number.
- Left lookups where the value to return is before the lookup column.
- Workbooks that need compatibility across Excel and Google Sheets.
Not for
- Returning every matching row. Use FILTER or QUERY when you need a list of rows.
- Modern Excel files where XLOOKUP is available and easier for your team to read.
- Multiple-criteria lookups unless you are using a helper key or a tested array pattern.
Useful formula variations
=INDEX(Products!D2:D100, MATCH(A2, Products!A2:A100, 0))Looks up A2 in the SKU column and returns the aligned value from the Price column.
=INDEX(Products!A2:A100, MATCH(F2, Products!B2:B100, 0))Looks up an item name in column B and returns the SKU from column A.
=IFERROR(INDEX(Products!D2:D100, MATCH(A2, Products!A2:A100, 0)), "Not found")Wrap the formula in IFERROR when a missing lookup value should show a friendly message.
=INDEX('Product List'!D2:D100, MATCH(A2, 'Product List'!A2:A100, 0))Use quoted sheet names when the tab name contains spaces.
Sample data
| SKU | Item | Category | Price |
|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 |
| A-101 | Mouse | Hardware | 25 |
| B-200 | Desk Mat | Office | 18 |
| B-201 | Notebook | Office | 7 |
Choose matching ranges
The lookup range and return range must cover the same rows. If the lookup range starts at row 2, the return range should start at the matching row 2.
Use full columns only when performance is acceptable. Bounded ranges such as A2:A100 and D2:D100 are easier to audit.
Left lookup use case
INDEX MATCH does not require the return column to sit to the right of the lookup column. That makes it useful for SKU lookup, owner lookup, and older workbooks where XLOOKUP is not available.
For example, match an item name in Products!B2:B100 and return the SKU from Products!A2:A100.
Excel and Google Sheets support
The generated formula uses INDEX, MATCH, and exact match mode, which are supported in both Excel and Google Sheets.
If your team uses newer Excel or Google Sheets, XLOOKUP can be shorter. INDEX MATCH remains useful when you need compatibility or a familiar legacy pattern.
Returned results from the sample data
| Lookup setup | Generated pattern | Returned result |
|---|---|---|
| A2 is A-100, lookup SKU, return Price | INDEX price range plus MATCH SKU range | 49 |
| F2 is Keyboard, lookup Item, return SKU | INDEX SKU range plus MATCH Item range | A-100 |
| F2 is Notebook, lookup Item, return Category | INDEX Category range plus MATCH Item range | Office |
When to use INDEX MATCH
| Need | Use this | Reason |
|---|---|---|
| Return a value to the left | INDEX MATCH or XLOOKUP | VLOOKUP cannot return from a column before the lookup column. |
| Avoid column index numbers | INDEX MATCH | The return range is explicit, so inserted columns are less likely to break the formula. |
| Return all matching rows | FILTER or QUERY | INDEX MATCH returns one aligned value, usually the first exact match. |
| Simplest modern lookup | XLOOKUP | XLOOKUP can include not-found handling in the same function. |
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| #N/A result | MATCH cannot find an exact value in the lookup range. | Check spelling, extra spaces, data types, and whether the lookup value exists in the lookup range. |
| Wrong value returned | The lookup range and return range start on different rows or cover different row counts. | Align the ranges row by row, such as A2:A100 with D2:D100. |
| Only the first duplicate is returned | MATCH returns the first exact match. | Use FILTER when you need every matching row instead of one value. |
Common mistakes
- The lookup range and return range should align row by row.
- Leaving out the 0 in MATCH can create approximate-match behavior.
- For multiple criteria, combine criteria columns with ampersands or use a helper key.
- Hidden spaces and mixed text-number IDs can make exact matches fail.
Related formulas
FAQ
Does INDEX MATCH work in Google Sheets?
Yes. Google Sheets supports INDEX, MATCH, and exact match mode with the same formula structure.
Can INDEX MATCH look left?
Yes. Put the left-side column in the INDEX return range and the lookup column inside MATCH.
Why does INDEX MATCH return #N/A?
MATCH did not find an exact value. Check extra spaces, mixed text-number IDs, spelling, and whether the lookup value exists.
Should I use INDEX MATCH or XLOOKUP?
Use XLOOKUP when your workbook supports it and you want shorter formulas. Use INDEX MATCH for compatibility, legacy workbooks, and teams already using the pattern.
Are these formulas generated with AI?
No. The builder uses built-in spreadsheet rules in your browser and does not call an AI service.
Do I need to upload my spreadsheet?
No. Enter ranges and criteria manually. The site does not upload, store, or process spreadsheet files.