INDEX 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 quickly find specific sales data points from a large table without scrolling through all the data.

📊 Data: You have a table showing monthly sales figures for different products across several regions.

🎯 Deliverable: Create formulas using the INDEX function to retrieve sales values based on row and column positions.

Sample Data

Product	Region	Jan	Feb	Mar
Apples	North	120	135	150
Apples	South	100	115	130
Bananas	North	90	95	100
Bananas	South	80	85	90
Cherries	North	60	70	75
Cherries	South	55	65	70

1

Step 1: Identify the range of sales data including months only (columns Jan to Mar).

Select cells C2:E7 as the data range for sales figures.

Expected Result

Range C2:E7 contains all sales numbers for Jan, Feb, and Mar.

2

Step 2: Use the INDEX function to find the sales for Apples in the South region for February.

=INDEX(C2:E7, 2, 2)

Expected Result

115

3

Step 3: Use the INDEX function to find the sales for Bananas in the North region for March.

=INDEX(C2:E7, 3, 3)

Expected Result

100

4

Step 4: Use the INDEX function to find the sales for Cherries in the South region for January.

=INDEX(C2:E7, 6, 1)

Expected Result

55

5

Step 5: Explain how the row and column numbers correspond to the data range.

Row number corresponds to the product-region row in the range; column number corresponds to the month (1=Jan, 2=Feb, 3=Mar).

Expected Result

Understanding of how to pick correct row and column numbers for INDEX.

Final Result

Sales Data Table (Jan-Mar)

+----------+--------+-----+-----+-----+
| Product  |Region  | Jan | Feb | Mar |
+----------+--------+-----+-----+-----+
| Apples   | North  | 120 | 135 | 150 |
| Apples   | South  | 100 | 115 | 130 |
| Bananas  | North  |  90 |  95 | 100 |
| Bananas  | South  |  80 |  85 |  90 |
| Cherries | North  |  60 |  70 |  75 |
| Cherries | South  |  55 |  65 |  70 |
+----------+--------+-----+-----+-----+

Example INDEX results:
- Apples South Feb: 115
- Bananas North Mar: 100
- Cherries South Jan: 55

✓The INDEX function can quickly retrieve specific sales data by row and column numbers.

✓Row numbers correspond to the product-region rows in the selected range.

✓Column numbers correspond to the months (Jan=1, Feb=2, Mar=3).

✓This method avoids scrolling and searching manually in large tables.

Bonus Challenge

Create a formula using INDEX combined with MATCH to find sales for any product and region dynamically.

Show Hint

Use MATCH to find the row number for the product-region and the column number for the month, then use INDEX with those numbers.