MATCH function 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 position of specific products in the sales list to quickly locate their sales data.

📊 Data: You have a list of product names in column A and their sales amounts in column B.

🎯 Deliverable: Create formulas using the MATCH function to find the row numbers of given products in the list.

Sample Data

Product	Sales
Apples	120
Bananas	150
Cherries	90
Dates	60
Elderberries	30
Figs	80
Grapes	110
Honeydew	70

1

Step 1: Select a cell where you want to find the position of 'Cherries' in the product list.

=MATCH("Cherries", A2:A9, 0)

Expected Result

3

2

Step 2: Select another cell to find the position of 'Grapes' in the product list.

=MATCH("Grapes", A2:A9, 0)

Expected Result

7

3

Step 3: Try to find the position of 'Oranges' which is not in the list to see how MATCH behaves.

=MATCH("Oranges", A2:A9, 0)

Expected Result

#N/A (Not found error)

4

Step 4: Select a cell to find the approximate position of 'Dates' in the product list using approximate match.

=MATCH("Dates", A2:A9, 1)

Expected Result

4

Final Result

Product List Positions
----------------------
Cherries position: 3
Grapes position: 7
Oranges position: #N/A (not found)
Dates approximate position: 4

✓MATCH function returns the position of a value in a range.

✓Exact match (0) finds the exact product or returns #N/A if not found.

✓Approximate match (1) finds the largest value less than or equal to the lookup value when data is sorted ascending.

Bonus Challenge

Use MATCH combined with INDEX to retrieve the sales amount for a given product.

Show Hint

Use MATCH to find the row number, then INDEX to get the sales from column B.