Chapter 07 · 8 min · Basic
When a lookup finds nothing
You have a list of sales with product codes on it, and somebody else keeps the list of what each code means. So you write a lookup, drag it down the column, and some of the cells come back #N/A.
The instinct is to make the error go away — wrap it, hide it, call it Other. That is the wrong instinct. #N/A is not a broken formula, it is a finding: the reference list you were given does not cover the data you were given, and somebody needs to know that before anybody reports a number.
By the end of this you will be able to say how many rows failed, how much money is behind them, and — the harder one — spot the rows that matched something and still told you nothing.
The grid
Ten sales rows in A1:C11 — product_code, units, amount_myr. Beside them in E1:F6 is the reference list somebody in the product team maintains: five codes, and what each one is. Work in column H.
The lookup that works
XLOOKUP takes three things: the value you are looking for, the column to look in, and the column to bring back from. Here that is the code on row 2, the reference list's own code column, and its category column.
The $ signs pin the reference list in place, so the formula can be dragged down the sales rows without the reference range sliding down with it.
Run it. Row 2 is P-001, the list says Drinks, and that is what comes back. Every lookup you will ever write looks like this when it works — which is why the interesting part is what happens when it does not.
Row 2 carries code P-001, and the reference list has a row for it. What comes back?
Scroll to see all 8 columns →
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | product_code | units | amount_myr | code | category | |||
| 2 | P-001 | 2 | 12.50 | P-001 | Drinks | =XLOOKUP(A2,$E$2:$E$6,$F$2:$F$6) | ||
| 3 | P-002 | 1 | 8.00 | P-002 | Snacks | |||
| 4 | P-003 | 3 | 24.90 | P-003 | Household | |||
| 5 | P-001 | 2 | 12.50 | P-007 | ||||
| 6 | P-014 | 5 | 45.00 | P-009 | Frozen | |||
| 7 | P-002 | 2 | 16.00 | |||||
| 8 | P-007 | 3 | 33.75 | |||||
| 9 | P-003 | 3 | 24.90 | |||||
| 10 | P-014 | 5 | 42.50 | |||||
| 11 | P-001 | 1 | 6.25 |
Basic
The rest of this chapter is Basic
The Excel tutorial and Spreadsheet Fundamentals are free here, forever. This track is the paid half: what to actually do with a spreadsheet once somebody sends you one, from the first look to a summary you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02The one that finds nothing
- 03How much is behind the gap
- 04Asking the question the other way round
- 05The failure that does not look like one
- 06What to do with what you found