Lookup formula page
INDEX MATCH Left Lookup Formula | Excel Sheets
Use INDEX MATCH for a left lookup when the value you want to return sits before the lookup column. This works in Excel and Google Sheets and is useful when VLOOKUP cannot look left.
Create an INDEX MATCH left lookup that returns a value from a column to the left.
If F2 is Keyboard, the formula returns A-100 from the SKU column to the left of the Item column.
Copy formulas
=INDEX(A2:A100, MATCH(F2, B2:B100, 0))=INDEX(A2:A100, MATCH(F2, B2:B100, 0))Excel and Google Sheets use the same INDEX plus MATCH pattern here. MATCH returns a row position, and INDEX can return from a range on either side of the lookup column.
Example data
| SKU | Item | Category | Price | Stock |
|---|---|---|---|---|
| A-100 | Keyboard | Hardware | 49 | 18 |
| A-101 | Mouse | Hardware | 25 | 32 |
| B-200 | Desk Mat | Office | 18 | 9 |
| B-201 | Notebook | Office | 7 | 64 |
If F2 is Keyboard, the formula returns A-100 from the SKU column to the left of the Item column.
How the formula works
- MATCH finds F2 inside the lookup range B2:B100.
- INDEX returns from A2:A100 at the same row position.
- The return range can be left of the lookup range, which is why this pattern solves left lookups.
- The 0 in MATCH forces exact match for names, IDs, SKUs, or other unsorted keys.
| Syntax piece | Role in the formula |
|---|---|
| A2:A100 | The return range containing the SKU values to the left of the item names. |
| F2 | The item name to find, such as Keyboard. |
| B2:B100 | The lookup range containing item names on the same rows as the SKU range. |
| 0 | Requires MATCH to find the item name exactly. |
Verified examples
=INDEX(A2:A100, MATCH(F2, B2:B100, 0))Excel: Enter Keyboard in F2 and use the sample SKU and Item columns. Returns: A-100
=IFERROR(INDEX(A2:A100, MATCH(F2, B2:B100, 0)), "Not found")Google Sheets: Enter Monitor in F2; that item is not present in the sample table. Returns: Not found
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| INDEX returns a value from the wrong row | The return and lookup ranges start or end on different worksheet rows. | Align the ranges, such as A2:A100 with B2:B100, before filling the formula down. |
| MATCH returns #N/A for an existing name | The source or lookup cell contains hidden spaces or inconsistent text normalization. | Compare the text lengths and clean both values before changing the INDEX range. |
| The first of several duplicate names is returned | MATCH with 0 stops at the first exact match. | Use a multiple-criteria lookup or FILTER when the item name is not unique enough to identify one row. |
When not to use this formula
- Use FILTER rather than INDEX MATCH when one key should return several matching rows.
Alternatives
| Alternative | When to use it |
|---|---|
| XLOOKUP Left Lookup Formula | Use in modern workbooks when a shorter left-lookup formula and built-in fallback are preferred. |
| INDEX MATCH Formula Builder | Use to select and validate the lookup and return ranges before copying a formula. |
Related formulas
Official references
- Look up values with VLOOKUP, INDEX, or MATCH from Microsoft
- INDEX function from Microsoft
FAQ
Why can INDEX MATCH return a column to the left?
INDEX receives its own return range, so that range does not have to sit to the right of the column searched by MATCH.
What does the zero in MATCH mean?
Zero requires an exact match for the value in F2 and is appropriate for item names, SKUs, IDs, and other unsorted keys.