Dashboard lookup with a friendly miss (XLOOKUP)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
The dashboard asks for one rep by name from F1 and shows their revenue in F2. Names and revenue are in A2:B5. If the name is not on the team, the cell should read "Not on team" instead of an error.
One formula handles both the hit and the miss — no separate IF around a lookup.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | F | |
|---|---|---|---|---|---|
| 1 | Rep | Revenue | Deals | Rep | Cleo |
| 2 | Ada | 5000 | 12 | Revenue | |
| 3 | Ben | 3800 | 9 | ||
| 4 | Cleo | 4200 | 11 | ||
| 5 | Dana | 2900 | 7 |
Notes
XLOOKUP takes the not-found value as its fourth argument, so the fallback is part of the lookup instead of an IFERROR wrapper. The older INDEX+MATCH route needs IFERROR for the same behaviour.
How to approach
Your answer goes in F2.
- Target cell is F2
- XLOOKUP takes lookup value, lookup range, return range
- The fourth argument is what to return when nothing matches
- Shape: =XLOOKUP(?, ?, ?, ?)
Function guide
FAQ
How do I show a message instead of #N/A?
=XLOOKUP(key, keys, values, "Not on team"). With INDEX+MATCH, wrap the whole thing in IFERROR.
Why does my lookup return a number for a name I typed with a trailing space?
It would not — a trailing space makes a different string, so the lookup misses. Trim the key, or trim the source column.