INDEX MATCH in Excel: The Lookup That Works Left
VLOOKUP cannot return a value to the left of the column it searches. INDEX MATCH can. Build MATCH first to find the row, wrap it in INDEX to return the product name, then lock the lists and fill it down.
Try it yourself
Copy these tables into a blank sheet, then follow the steps below.
Paste at cell A1:
| Product | Price | Code |
|---|---|---|
| Desk Lamp | $35 | P104 |
| Office Chair | $149 | P101 |
| Notebook | $4 | P106 |
| Stapler | $12 | P102 |
| Monitor Stand | $45 | P105 |
| Wall Clock | $22 | P103 |
Paste at cell E1:
| Order | Code | Product |
|---|---|---|
| ORD-1 | P102 | |
| ORD-2 | P106 | |
| ORD-3 | P101 | |
| ORD-4 | P105 |
Step by step
- INDEX MATCH. You know a product code, but the product name sits in a column to its left. VLOOKUP can't return it. INDEX MATCH can, because it works in any direction.
- The product list. The product list is in A1:C7. It has the product name in column A, the price in column B and the code in column C, on the far right.
- The order list. The order list in E1:G5 has 4 orders. Each order only has a product code, in column F. We want the product name in column G.
- VLOOKUP can't look left. VLOOKUP searches the first column of a range and only returns columns to its right. Here the code is in the last column, and the names in A2:A7 are to its left, so VLOOKUP can't reach them.
- MATCH. Build it in 2 parts. Part 1 is MATCH, which finds the code and returns its position. In G2, type =MATCH(F2,$C$2:$C$7,0). The 0 means exact match. It returns 4, because code P102 is in C5, the 4th row of the list. MATCH gives you the row number, not the name.
G2
=MATCH(F2,$C$2:$C$7,0) - INDEX. Part 2 is INDEX. Give it a column and a row number, and it returns whatever is in that row. Here the column is the product names in A2:A7. Now put the MATCH inside INDEX, so MATCH supplies the row number. The formula in G2 becomes =INDEX($A$2:$A$7,MATCH(F2,$C$2:$C$7,0)).
G2
=INDEX($A$2:$A$7,MATCH(F2,$C$2:$C$7,0)) - The answer. G2 shows Stapler. MATCH found the row, and INDEX returned the name from column A, to the left of the code.
- Fill down. $A$2:$A$7 and $C$2:$C$7 are absolute references, so both lists stay put when you copy the formula. Drag the fill handle from G2 down to G5.
- Every product. Every order now has its product: Stapler, Notebook, Office chair and Monitor stand. All of them were looked up to the left of the code.
- It stays live. The formula stays live. Change the code in F3 to P103, and G3 changes to Wall clock by itself.
F3
P103 - Mark the formulas. A useful habit is to give formula cells a light fill. Here G2:G5 gets a light green fill (#E2EFDA), so the team knows not to type over them.
- MATCH then INDEX. MATCH finds the row number. INDEX returns the value from that row, in any column, so the lookup works left or right. Lock both lists with absolute references before you fill down.
Common mistakes
- Leaving out the 0 in MATCH. If you leave out the last part, MATCH does an approximate match. That needs a sorted list, and on an unsorted code list it can quietly return the wrong row. End MATCH with 0 for an exact match.
- Not locking the lists. Without absolute references, C2:C7 becomes C3:C8 as you fill down. The lower rows then search the wrong range and can return #N/A or the wrong product. Write $A$2:$A$7 and $C$2:$C$7, or press F4 after selecting a range.
- INDEX and MATCH ranges that start on different rows. If INDEX uses A1:A7 but MATCH uses C2:C7, every answer is off by 1 row and you get the name above the right one. Make both ranges start and end on the same rows.
- A code that isn't in the list. MATCH returns #N/A, and so the whole formula does too. Check the code for typos or extra spaces, or wrap the formula in IFNA to show a message such as Not found.
When a business analyst uses this
A stakeholder register has the stakeholder ID in its last column and the names to the left, and another team owns that sheet, so you can't move its columns. In your action log, which only holds IDs, INDEX MATCH pulls each stakeholder's name across without changing their sheet.
Questions people ask
Is INDEX MATCH available in Excel 2016?
Yes. INDEX and MATCH are in every version of Excel, including 2016, 2019 and Microsoft 365, so the formula works in any of them.
What is the difference between INDEX MATCH and XLOOKUP?
Both can look left or right. XLOOKUP does it in 1 function, but it needs Microsoft 365, Excel 2021 or later, or Excel for the web. INDEX MATCH also works in older versions.
How do you use INDEX MATCH between two sheets?
Put the sheet name in front of each range, for example =INDEX(Products!$A$2:$A$7,MATCH(F2,Products!$C$2:$C$7,0)). Excel adds the sheet name for you if you click the other sheet while you type the formula.
