INDEX and MATCH combination in Google Sheets - 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 by looking up their names in the sales data.

📊 Data: You have a table with Product Names in column A and Sales Amounts in column B for the last month.

🎯 Deliverable: Create a formula using INDEX and MATCH to find the sales amount for any product name entered in a separate cell.

Sample Data

Product Name	Sales Amount
Apples	150
Bananas	200
Cherries	120
Dates	90
Elderberries	60
Figs	110
Grapes	180

1

Step 1: Enter the product name you want to find the sales for in cell D2. For example, type "Cherries".

Expected Result

Cell D2 contains the text "Cherries".

2

Step 2: In cell E2, enter the formula to find the sales amount for the product name in D2 using INDEX and MATCH.

=INDEX(B2:B8, MATCH(D2, A2:A8, 0))

Expected Result

If D2 is "Cherries", E2 shows 120.

3

Step 3: Test the formula by changing the product name in D2 to "Grapes" and check the sales amount in E2.

Expected Result

E2 updates to 180.

Final Result

Product Lookup

D2: Product Name to find
E2: Sales Amount

+------------+--------------+
| Product    | Sales Amount |
+------------+--------------+
| Apples     | 150          |
| Bananas    | 200          |
| Cherries   | 120          |
| Dates      | 90           |
| Elderberries| 60          |
| Figs       | 110          |
| Grapes     | 180          |
+------------+--------------+

Lookup Example:
D2 = "Cherries"
E2 = 120

✓The INDEX and MATCH formula correctly finds the sales amount for any product name entered.

✓Changing the product name in D2 updates the sales amount in E2 automatically.

✓This method is flexible and avoids errors from sorting or using VLOOKUP limitations.

Bonus Challenge

Modify the formula to return "Not Found" if the product name in D2 does not exist in the list.

Show Hint

Use IFERROR around the INDEX and MATCH formula, like IFERROR(INDEX(...), "Not Found")