Dynamic Dashboard with a Dropdown

Excel Formulas › Dashboards & Reporting

All versionsINDEX

Pick a region from a dropdown and every KPI updates. A data-validation list drives an INDEX/MATCH that pulls each metric for the chosen item — the backbone of an interactive dashboard.

Quick formula: pull a metric for the selected item:
=INDEX(metric_column, MATCH(selected, item_column, 0))
MATCH finds the chosen item's row; INDEX returns its value from the metric column. One dropdown drives all KPIs.

Functions used (tap for the full reference guide):

INDEXMATCH

The example

Select "West" → its revenue, units, margin.

AB
1KPIValue
2Revenue (West)= INDEX/MATCH

The formula

The formula:

=INDEX(metric_col, MATCH($selected_cell, item_col, 0)) // selector drives every KPI

How it works

How it works:

  1. Put the choices in a data-validation dropdown — the selector cell.
  2. MATCH(selected, items, 0) finds the row of the chosen item.
  3. INDEX(metric, that_row) returns the metric for it — repeat for each KPI.
  4. Lock the selector cell with absolute references so every KPI reads the same choice.

One selector, many cards. Build each KPI tile as an INDEX/MATCH pointed at the same dropdown cell, and the whole dashboard re-renders when the user changes the selection — no macros. Add charts that reference these driven cells and they update too. It’s the simplest interactive dashboard pattern and works in every Excel version.

Try it: interactive demo

Live demo

Pick a region.

Revenue · Units

Variations

Two-way (item × metric)

Grid lookup:

=INDEX(grid, MATCH(item,rows,0), MATCH(kpi,cols,0))

XLOOKUP version

365:

=XLOOKUP(selected, items, metric)

Safe if not found

No match:

=IFERROR(INDEX(m, MATCH(sel, i, 0)), "—")

Pitfalls & errors

Lock the selector. Use an absolute reference so all KPIs read the same cell.

Exact match. Use 0 as MATCH’s third argument.

Guard misses. Wrap in IFERROR so a typo doesn’t litter #N/A across the dashboard.

Practice workbook

📊
Download the free Dynamic Dashboard with a Dropdown practice workbook
A dashboard-dropdown sheet with the two-way, XLOOKUP, and safe variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a dropdown-driven dashboard in Excel?
Use a data-validation list as the selector and =INDEX(metric_col, MATCH($selected, item_col, 0)) for each KPI.
How do I look up by item and metric at once?
Use a two-way INDEX/MATCH: =INDEX(grid, MATCH(item,rows,0), MATCH(kpi,cols,0)).
How do I avoid #N/A on the dashboard?
Wrap each lookup in IFERROR with a placeholder, e.g. IFERROR(..., "—").

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: INDEX & MATCH 2D · Data validation dropdown · Metric toggle

Function references: INDEXMATCH