Excel INDEX and MATCH: Look Up Values with a Flexible Layout
On this page
INDEX and MATCH split a lookup into two steps: find a key's position, then return the value at that position. This is useful when the return column sits to the left of the lookup column.
Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.
Enter a reference list
Use Price, Code and Product in A1:C5. Add 120, P101, Notebook; 25, P102, Pen; 80, P103, Folder; and 150, P104, Marker. In E1 type Code to find and put P103 in E2.
The code is in B, while price is to its left in A. A traditional VLOOKUP searching B:C cannot return A. INDEX and MATCH can refer to the lookup and return columns separately.
Find the position first
In F2 enter =MATCH(E2,$B$2:$B$5,0). The result is 3 because P103 is the third entry within the selected range. It is not worksheet row 3: the matching record is in worksheet row 4.
The final 0 requests an exact match. Omitting or changing it invokes different matching behaviour, which can require sorted input. Product codes normally need an exact match to the intended identifier.
Return the corresponding value
=INDEX($A$2:$A$5,3) returns 80. Combine the steps as =INDEX($A$2:$A$5,MATCH(E2,$B$2:$B$5,0)). MATCH supplies the position, and INDEX returns that position's price.
The two ranges must cover the same records in the same order. If one starts a row lower, a valid position can return another product's price. A plausible number is not enough evidence; inspect range alignment.
Copy and test
Enter P101 in E3 and P104 in E4, then copy the combined formula down. The source ranges remain fixed while the lookup cell moves. The results should be 120 and 150.
Try P999. MATCH returns #N/A because no exact key exists. Use IFNA for a clearer display if needed, but first confirm that the key is genuinely absent rather than padded with spaces or stored in an inconsistent type.
Protect the meaning of the key
MATCH returns the first exact matching position. Duplicate product codes with different prices require a data decision, not a more confident lookup formula. Use a reference list with unique keys and investigate duplicate entries.
Leading zeros can matter in identifiers, so do not convert every key into a number simply to make a match work. Normalise types deliberately and preserve the original data during review.
Maintain the complete range
A new product below row 5 is outside these fixed references. Extend both ranges together or use suitable Table references. A syntactically correct lookup against an incomplete list can still miss valid records.
Try it yourself
With P102 in E2, return the product name instead of the price. Which part of the formula changes, and what remains the same?
Check your answer
Use =INDEX($C$2:$C$5,MATCH(E2,$B$2:$B$5,0)). The result is Pen.
Only the INDEX return range changes from prices to names. MATCH still searches the code column with exact matching. Separating these two roles makes the formula easier to adapt and inspect.
Keep exploring: Excel learning path.