INDEX function in Excel - Dashboard Guide

Dashboard Mode - INDEX function

Dashboard Goal

Find specific sales data from a table by selecting the row and column number using the INDEX function.

Sample Data

Product	Q1 Sales	Q2 Sales	Q3 Sales
Apples	120	135	150
Bananas	80	90	100
Cherries	200	210	220
Dates	150	160	170
Elderberries	90	95	100

Dashboard Components

• KPI Card: Total Q2 Sales for Bananas
  Formula: =INDEX(B2:D6,2,2)
  Explanation: Returns the value in 2nd row, 2nd column of the range B2:D6 (Bananas Q2 Sales = 90)
• KPI Card: Q3 Sales for Cherries
  Formula: =INDEX(B2:D6,3,3)
  Explanation: Returns the value in 3rd row, 3rd column (Cherries Q3 Sales = 220)
• Table: Extract entire Q1 Sales column
  Formula in E2 and copied down: =INDEX(B2:B6,ROW()-1)
  Explanation: Returns Q1 sales for each product by row number
• Dynamic Cell: Select Product and Quarter to get sales
  Formula: =INDEX(B2:D6,MATCH(G2,A2:A6,0),MATCH(G3,B1:D1,0))
  Explanation: Uses MATCH to find row and column numbers based on product name in G2 and quarter in G3

Dashboard Layout

+----------------------+----------------------+
| Total Q2 Sales Bananas| Q3 Sales Cherries    |
|      (INDEX result)   |    (INDEX result)    |
+----------------------+----------------------+
| Extracted Q1 Sales    | Dynamic Sales Lookup |
| (Q1 sales column)     | (Select product/quarter)
+----------------------+----------------------+

Interactivity

User enters product name in cell G2 and quarter name in G3. The dynamic sales lookup updates automatically using the INDEX formula combined with MATCH to show the correct sales value.

The other KPI cards and extracted column show fixed values from the data table.

Self Check

If you change the product name in G2 to "Dates" and quarter in G3 to "Q1", what value appears in the dynamic sales lookup cell?

Answer: 150 (Dates Q1 Sales)

Key Result

Dashboard shows how to use INDEX to find specific sales data by row and column numbers, including dynamic lookup by product and quarter.