How to Use FILTER + XLOOKUP for Dynamic Dashboards
February 3rd, 2026
Dashboards usually need two things: a filtered dataset and a lookup that turns an ID into a label, owner, or category. This post shows a simple, repeatable pattern that combines FILTER and XLOOKUP to build dynamic views that update automatically.
The scenario
You have a table of deals:
- Column A: Deal ID
- Column B: Owner ID
- Column C: Stage
- Column D: Amount
And a separate table of owners:
- Column G: Owner ID
- Column H: Owner name
You want a dashboard that shows only "Open" deals and the owner name.
Step 1: Filter the rows you need
=FILTER(A2:D, C2:C="Open")
This returns only the rows where Stage is "Open". Keep this output in a separate tab so you can reuse it.
Step 2: Add a readable owner name
If your filtered data is in Filtered!A2:D, use XLOOKUP to map Owner IDs to names:
=ARRAYFORMULA(XLOOKUP(Filtered!B2:B, Owners!G2:G, Owners!H2:H, "Unknown"))
This returns the owner name for each filtered row. ARRAYFORMULA keeps it dynamic.
Step 3: Combine into a final view
You can stitch it together using an array literal:
={Filtered!A2:A, Filtered!C2:C, Filtered!D2:D, ARRAYFORMULA(XLOOKUP(Filtered!B2:B, Owners!G2:G, Owners!H2:H, "Unknown"))}
Now you have: Deal ID, Stage, Amount, Owner name.
Tips for reliability
- Use exact matches with XLOOKUP (default behavior).
- Keep your ranges the same size.
- If you see #N/A errors, check for whitespace in IDs.