---
title: "Build a dynamic dashboard with XLOOKUP and one drop-down"
date: 2026-07-14T00:00:00Z
updated: 2026-07-09T21:42:18Z
tags: ["Excel", "XLOOKUP", "Formulas", "Reporting", "Data Validation", "Dashboards"]
canonical: https://bianca.codes/blog/build-dynamic-dashboard-xlookup-dropdown/
---

# Build a dynamic dashboard with XLOOKUP and one drop-down

_One drop-down cell, four XLOOKUP formulas, and every number on the dashboard updates itself - no PivotTable, no slicer, no VBA in sight._

The deliverable: one cell with a drop-down list, and every number on the dashboard below it wired to that cell through XLOOKUP. Change the drop-down, every card updates. No PivotTable, no slicer, no VBA - a table and four formulas that all point at the same cell.

You need Excel with a Microsoft 365 subscription or Excel 2021 (XLOOKUP isn't in perpetual-licence Excel before that), plus a clean summary table before you start: one row per thing you're picking between, one column per metric you want on the dashboard. If your data isn't summarised yet, that's a Power Query or PivotTable problem to solve first - this post starts on the other side of it.

## Why this approach

The obvious alternative is a PivotTable with a slicer, and for exploring data that's still the right call. But a slicer builds a report you browse, not a one-page dashboard with cards in fixed positions, sized the way you want them. The other obvious alternative is worse: chain `IF(Store="North", ..., IF(Store="South", ...))` for every card, which is exactly the trap I already made the case against in [Your nested IF statements are technical debt](/blog/nested-if-statements-are-technical-debt/) (Jul 1). A dashboard built on nested IFs needs a formula edit every time you add a store. A dashboard built on XLOOKUP against a table needs nothing - add a row, and the drop-down picks it up on its own.

## Step 1: Structure the source as a clean table

Select your summary data and press `Ctrl + T`. Confirm "My table has headers," then rename it from the generic `Table1` on the Table Design tab - `tblStoreSummary` is what the formulas below use. One row per store, one column per metric: Store, Revenue, Units Sold, Avg Order Value, Top Product.

The store name column is the key you'll look up on, so it has to be unique. XLOOKUP returns the first match it finds and never mentions the second one. Sort the column and eyeball it for duplicates before you build a single formula on top of it.

## Step 2: Add the drop-down that drives everything

Pick the cell that's going to be the control - `B1` works, with a label like "Select store" next to it. Data tab, Data Validation, allow **List**, and set the source to:

```vba
=tblStoreSummary[Store]
```

Because the source is a table column and not a fixed range, the drop-down grows the moment a new store is added to the table. No named range to maintain, nothing to remember to extend.

## Step 3: Write the first XLOOKUP

In the cell for your first card:

```vba
=XLOOKUP($B$1, tblStoreSummary[Store], tblStoreSummary[Revenue], "Pick a store")
```

Four arguments: the lookup value (the drop-down cell), the lookup array (the Store column), the return array (the Revenue column), and the if-not-found value - XLOOKUP takes that natively, so you're not wrapping the whole thing in `IFERROR` the way you would with VLOOKUP.

The `$B$1` matters more than it looks like it should. Type `B1` without the dollar signs, copy the formula down to the next card, and Excel shifts the reference to `B2` - which is empty, so the card underneath silently shows your fallback text instead of a number. Click into the formula bar with your cursor on `B1` and press `F4` to cycle it through `B1`, `$B$1`, `B$1`, `$B1` - stop on `$B$1`. [The F4 trick that locks cell references in one keystroke](/blog/f4-absolute-references-click-bait/) (Jul 4) covers the other two variants and when mixed locking is actually what you want.

## Step 4: Copy the formula across every card

Paste the Step 3 formula into each remaining card cell. Because `$B$1` is locked, every pasted copy still points at the same drop-down. What doesn't carry over automatically is the return column - structured references don't shift with position the way `A1`-style references do, so you still swap `Revenue` for `Units Sold`, `Avg Order Value`, and `Top Product` by hand in each cell. One argument changes per card. Nothing else does.

## Step 5: Handle the blank state

Before anyone touches the drop-down, `$B$1` is empty, and without the fourth argument every card would show `#N/A` on first open - not a great first impression. The `"Pick a store"` fallback from Step 3 means the dashboard opens looking intentional instead of broken, and clears itself the moment someone makes a selection.

## Common mistakes

### **Duplicate values in the lookup column**

A typo, a copy-pasted row, a store that got renamed but the old row never got cleaned up - any of these leaves two rows with the same name in the Store column. XLOOKUP returns the first match and gives no indication it skipped a second one. The dashboard keeps working. It's quietly wrong. Check for duplicates before you wire up a single card, not after someone notices the numbers look off.

### **Forgetting to lock the drop-down cell**

This is the Step 3 mistake, and it's the one that shows up after the dashboard already looked finished. You built card one, it worked, you copied it to cards two through four, and now half the dashboard is blank or stuck on the fallback text. Check every card's lookup value argument for a bare `B1` where you meant `$B$1`.

## Where to go from here

This closes two loops. The nested IF Hot Take argued you shouldn't chain nine IFs to switch between cases - this is the table-and-lookup version of that argument, actually built. And the reference-locking in Step 3 is the exact scenario the F4 tip was written for: copy an XLOOKUP with an unlocked lookup cell, and watch the card one row down quietly break.

## Frequently Asked Questions

### **Does this work with VLOOKUP instead of XLOOKUP?**

Not cleanly. VLOOKUP can't return a "not found" fallback natively, so you'd wrap every formula in `IFERROR`, and it breaks if anyone inserts a column inside the lookup range. INDEX/MATCH is the pre-XLOOKUP equivalent that doesn't have that second problem, but you still lose the built-in fallback argument. If you're on Excel 2019 or earlier, INDEX/MATCH plus IFERROR is the fallback - same structure, more typing.

### **Can one drop-down drive more than four cards?**

Yes - the number of cards equals the number of XLOOKUP formulas you write, and every one of them references the same `$B$1`. There's no practical limit beyond how much dashboard you want to lay out.

### **What happens when I add a new store to the table?**

Nothing you have to do. The drop-down list reads from the table column, so it grows the moment the table does. The XLOOKUP formulas already search the whole table, so a new row is available to look up as soon as it exists.

### **Why not just use a PivotTable and a slicer?**

A slicer-driven PivotTable is faster to build and better for a report someone browses interactively. Reach for the XLOOKUP version when you need a fixed one-page layout - cards in specific cells, sized and positioned the way you want - which is a layout PivotTables don't give you.

### **Does the drop-down have to reference a table column?**

No, but it should. A data validation list can point at a plain cell range, but then the list stops growing automatically the moment you add a row, and you're back to maintaining a range reference by hand every time the source changes.