INDEX-MATCH combination in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to find the sales amount for specific products based on their product IDs.

📊 Data: You have a table with Product IDs, Product Names, and Sales Amounts for the last month.

🎯 Deliverable: Create a formula using INDEX and MATCH to look up the sales amount for a given Product ID.

Sample Data

Product ID	Product Name	Sales Amount
101	Notebook	250
102	Pen	150
103	Backpack	300
104	Calculator	400
105	Desk Lamp	350

1

Step 1: Identify the column with Product IDs and the column with Sales Amounts in your data table.

Product IDs are in column A (A2:A6), Sales Amounts are in column C (C2:C6).

Expected Result

You know where to look for the data to use in the formula.

2

Step 2: Use the MATCH function to find the row number of the Product ID you want to look up.

=MATCH(104, A2:A6, 0)

Expected Result

4 (because Product ID 104 is in the 4th row of the range A2:A6)

3

Step 3: Use the INDEX function to get the Sales Amount from the Sales Amount column using the row number from MATCH.

=INDEX(C2:C6, MATCH(104, A2:A6, 0))

Expected Result

400 (the sales amount for Product ID 104)

4

Step 4: Combine the formula to look up sales amount for any Product ID entered in cell E2.

=INDEX(C2:C6, MATCH(E2, A2:A6, 0))

Expected Result

If E2 contains 103, the formula returns 300.

Final Result

Product ID Lookup
-----------------
Enter Product ID in E2
Formula in F2:
=INDEX(C2:C6, MATCH(E2, A2:A6, 0))

Example:
E2 = 105
F2 = 350

✓INDEX-MATCH is a powerful way to look up values in a table.

✓MATCH finds the position of the lookup value.

✓INDEX returns the value at that position from another column.

✓This method is more flexible than VLOOKUP because it can look left or right.

Bonus Challenge

Modify the formula to return 'Not Found' if the Product ID does not exist in the list.

Show Hint

Use the IFERROR function around the INDEX-MATCH formula.