Calderbank Logistics invoiced a week of pallet work at £5,590.15. The company's own rate card says the same ten jobs are worth £7,377.65, and every rate on that invoice came out of the card.
The card holds each lane twice: once at the standard rate, once at the next-day rate. The booking sheet looked each job up by lane code alone.
=XLOOKUP(A2,$J$2:$J$11,$L$2:$L$11) takes the first row whose lane matches and stops. The standard row was typed above the next-day row in every pair, so five next-day jobs were priced as standard work.
Invoiced from the sheet 5,590.15
Owed at the rate card 7,377.65
Five next-day jobs 1,787.50
Nothing errored. Every cell held a number, every number came off the rate card, and the only thing wrong was which of two rows the lookup had landed on.
| Measure | On the invoice | At the rate card |
|---|---|---|
| Jobs priced | 10 | 10 |
| Jobs at the right rate | 5 | 10 |
| Total | £5,590.15 | £7,377.65 |
| Shortfall | £1,787.50 | — |
A lookup on a key that repeats is not a formula that went wrong. It is a formula doing exactly what it was asked, on a question that has two answers and no way to say so.
01Ten Jobs, One Card, Two Rows a Lane
Ten Jobs, Five of Them at the Wrong Rate
One week of pallet work for a single account: the lane, the service it was booked at, the rate the booking sheet looked up, the rate the card actually holds for that lane and that service, the pallets moved, and what each row was invoiced at against what it was owed. Jobs are in A2:H11. The rate card sits beside them in J2:L11 — five lanes, each one entered once as Standard and once as Next day, standard first. The booking sheet looked each job up on the lane alone, so every next-day job was priced from the standard row. SUMPRODUCT(C2:C11,E2:E11) invoices £5,590.15, SUMPRODUCT(D2:D11,E2:E11) is the £7,377.65 the card says, and the £1,787.50 between them is five rows where the lookup chose for itself.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Columns C and D are the same rate card read two ways. C is what the sheet looked up on the lane code; D is the rate that lane carries for the service the job was booked at.
The two agree on every standard job and differ on every next-day one. Five rows out of ten, and the five that differ are the five worth the most per pallet.
The card itself sits in J2:L11 — ten rows, five lanes, each lane once as Standard and once as Next day:
| Lane | Standard | Next day |
|---|---|---|
| BRS-LDS | £38.50 | £61.00 |
| MAN-GLW | £44.00 | £69.50 |
| LDS-NCL | £29.75 | £47.25 |
| BHM-BRS | £33.20 | £52.80 |
| GLW-ABD | £36.90 | £58.40 |
Laid out as a grid it is obvious that a lane on its own cannot price a job. Laid out as ten rows down a column, which is how the sheet holds it, it looks like an ordinary lookup table.
Scenario: Put =COUNTIF($J$2:$J$11,J2) beside the first key of your own rate or price list and fill it down. Any answer above 1 is a row the lookup is choosing between without telling you.
02A Lookup Stops at the First Match
XLOOKUP and VLOOKUP both return one value, and both take the first row that satisfies the match. Neither has any way to mention that a second row would have matched too.
On a unique key that is exactly right. On a key that repeats it silently chooses, and what it chooses by is position in the table.
In this card the standard row sits above the next-day row for every lane, because that is the order somebody typed them in. Reverse the two and the same sheet overcharges every standard job instead.
XLOOKUPcan search from the bottom up, with-1as its sixth argument.VLOOKUPcannot search backwards at all.Searching from the bottom is not a fix. It swaps which of the two rows you get wrong, and it breaks on the day somebody adds a lane to the end of the card.
Scenario: Copy one of your lookups into a spare cell and give it a reversed search: =XLOOKUP(A2,$J$2:$J$11,$L$2:$L$11,,0,-1). If the answer changes, your key repeats and the formula has been picking a row for you.
03Finding Out Whether the Key Repeats
One formula answers it for the whole column. =SUMPRODUCT(--(COUNTIF($J$2:$J$11,$J$2:$J$11)>1)) counts the rows whose lane appears more than once, and on this card it returns 10.
Ten out of ten, because every lane is there twice. A card where only one lane repeats returns 2, and that is the dangerous one: nine lanes behave and one does not.
The same count across both columns says whether the pair is unique: =SUMPRODUCT(--(COUNTIFS($J$2:$J$11,$J$2:$J$11,$K$2:$K$11,$K$2:$K$11)>1)) returns 0 here.
A lookup key is only a key when that second number is 0. The first number tells you how badly you need a second criterion; the second tells you whether you have found it.
Scenario: Run both counts over your own lookup table, in two spare cells, before you write another formula against it. First above zero and second at zero is the signature of exactly this fault.
Try it in the grid04Fix One: Join the Two Columns Into a Key
The oldest fix still works everywhere. Build a helper column on the rate card that glues the two criteria together, build the same string in the booking sheet, and look that up instead:
Rate card, M2: =J2&"|"&K2
Booking sheet: =XLOOKUP(A2&"|"&B2,$M$2:$M$11,$L$2:$L$11,,0)
The separator is the whole trick. A character that can appear inside either value joins two different pairs into the same key, which is how a plain J2&K2 turns BRS + LDS01 and BRSLDS + 01 into one row.
Pick something the data cannot contain — a pipe or a tilde — and use the same one on both sides. The cost of this fix is a column to maintain on every table it touches.
Scenario: Add the helper column to a copy of your own rate card, write the joined lookup against it, and check that a row you know should be the expensive one now returns the expensive rate.
Try it in the grid05Fix Two: Multiply the Two Conditions
XLOOKUP will take an array where its lookup array goes, so both tests can live inside the formula and the helper column disappears:
=XLOOKUP(1,($J$2:$J$11=A2)*($K$2:$K$11=B2),$L$2:$L$11)
Each comparison returns ten TRUEs and FALSEs. Multiplying them turns the pair into ten 1s and 0s, and exactly one of them is 1 — the row where both tests passed.
That 1 at the front is what the formula looks for. Nothing to keep in step when a lane is added, and it reads as the sentence you would have said out loud.
XLOOKUParrived with Microsoft 365 and Excel 2021. In Excel 2019 and earlier it is not there at all, and the same idea is written withINDEXandMATCHinstead — the next section.
Scenario: Type the multiplied form beside one of your own two-criteria rows and compare it with the single-key answer next to it. Every row where the two differ is a row the old formula was getting wrong.
Try it in the grid06Fix Three: INDEX and MATCH on the Same Test
INDEX and MATCH take the same array of 1s and 0s, and they work in every version of Excel ever shipped:
=INDEX($L$2:$L$11,
MATCH(1,($J$2:$J$11=A2)*($K$2:$K$11=B2),0))
MATCH returns the position of the first 1 and INDEX reads that position out of the rate column. The 0 is the exact-match argument, and here it is not optional: the 1s and 0s are in no useful order.
In Microsoft 365 that is all it needs. In Excel 2019 and earlier it is an array formula, entered with Ctrl+Shift+Enter, and the curly braces Excel wraps round it are its own — typing them yourself does nothing.
Scenario: Write the INDEX/MATCH version beside the XLOOKUP one and confirm both return 61.00 on the first job. Then delete the 0 from MATCH and watch the answer go wrong rather than error.
07When the Answer Is a Total, Use SUMIFS
Half the lookups written on two criteria are not lookups at all. When the thing you want is a number to add up, SUMIFS takes criteria in pairs and never cares about row order:
=SUMIFS($L$2:$L$11,$J$2:$J$11,A2,$K$2:$K$11,B2)
On a card with one row per pair this returns the same rate the lookup should have. On a table where two rows genuinely match it returns their total, which is right for invoice lines and wrong for a rate.
COUNTIFS is the companion, and it is the check the lookup cannot do for itself. =COUNTIFS($J$2:$J$11,A2,$K$2:$K$11,B2) should answer 1 beside every rate on the sheet.
Anything other than 1 is a fault in the card, not in the formula. Two is a duplicate nobody has noticed; zero is a lane the card has never heard of, dressed up as #N/A or as whatever you told the lookup to say instead.
Scenario: Put that COUNTIFS in a column beside your own lookups and sort it. Read the rows that answer 0 and the rows that answer 2 before you read anything else on the sheet.
08When Two Criteria Still Match Several Rows
Sometimes the pair genuinely is not unique — a lane, a service and three price breaks by weight. A lookup cannot answer that, because the question has more than one answer and a lookup returns one.
FILTER returns all of them: =FILTER($L$2:$L$11,($J$2:$J$11=A2)*($K$2:$K$11=B2)). Spilled into empty space, three rows mean three matches, and the lookup you were about to write would have shown you one of the three.
When the rule is "the cheapest of them", say so in the formula: =MIN(FILTER($L$2:$L$11,($J$2:$J$11=A2)*($K$2:$K$11=B2))) is a decision written down. A lookup that happens to land on the cheapest row is a coincidence, and it lasts until somebody sorts the card.
Scenario: Spill a FILTER on your own two criteria into empty space and count the rows it returns. More than one is the moment to decide which one you want, in writing, rather than letting row order decide it.
09Exact Match Is Not the Default Everywhere
VLOOKUP's fourth argument decides whether it matches exactly, and leaving it out means TRUE. Approximate match on an unsorted card returns a confident wrong row rather than #N/A.
XLOOKUP is the other way round: its match mode defaults to exact. That is the right default, and on its own it is a reason to make the switch.
| Formula | Match | On an unsorted card |
|---|---|---|
=VLOOKUP(A2,$J$2:$L$11,3) | Approximate | A wrong row, no error |
=VLOOKUP(A2,$J$2:$L$11,3,FALSE) | Exact | #N/A when nothing matches |
=XLOOKUP(A2,$J$2:$J$11,$L$2:$L$11) | Exact | #N/A when nothing matches |
An approximate match on a repeating key is two faults in one cell, and the second one hides the first: the row you get is not even the first match, it is wherever the binary search happened to stop.
Scenario: Search the workbook for VLOOKUP( and read the end of each one. Any that stops at the column number is matching approximately; add ,FALSE and see which of them start returning #N/A.
10What Breaks a Joined Key
A joined key is text, and text is fussy. Three things break it, and none of them show on screen:
- A trailing space on one side. The card holds
"BRS-LDS "and the booking sheet holds"BRS-LDS", so the keys differ by one character.=TRIM(J2)&"|"&TRIM(K2)on both sides settles it. - A number on one side and text on the other. A lane code of
01182typed in one sheet and imported into the other arrives as 1182 in one of them.=TEXT(J2,"00000")on both sides makes them the same string. - Case.
XLOOKUPignores it, soNext dayandNEXT DAYmatch each other happily. If your data distinguishes them, a lookup will not.
The failure mode is #N/A on some rows and silence on the rest, which is the best kind of fault, because the #N/A is visible and a wrong rate is not.
Scenario: Put =SUMPRODUCT(--(LEN($J$2:$J$11)<>LEN(TRIM($J$2:$J$11)))) over your key column. Anything but 0 is a trailing space sitting there waiting to cost you a match.
11What a Miss Should Say
Every fix above returns #N/A when nothing matches, and that is the correct answer. A rate that cannot be found is not zero and it is not blank.
XLOOKUP's fourth argument is where you say what it should be instead. =XLOOKUP(1,($J$2:$J$11=A2)*($K$2:$K$11=B2),$L$2:$L$11,"NO RATE") puts a word on the invoice line rather than a number somebody might add up.
Wrapping the whole thing in IFERROR is the mistake. It catches the #N/A, and it also catches the #REF! from a deleted column and the #VALUE! from a rate stored as text, and turns all three into the same tidy blank.
IFNA catches only the miss and lets everything else through, which is the entire reason it exists.
Scenario: Replace every IFERROR wrapped round a lookup in your workbook with IFNA and recalculate. Any cell that turns into an error was hiding one all along.
12Seven Things That Bite
- Looking up on the column that names the thing. A product code, a lane, a customer name — every one of them repeats the moment the table gains a second dimension.
- Trusting a lookup because it returned a number. The failure here is a plausible rate from the wrong row, not an error anybody can see.
- Sorting the lookup table. With a repeating key the answer depends on row order, so a sort changes results without changing a formula.
- Reversing the search instead of fixing it.
-1picks the other row. It does not pick the right one. - Joining keys with a character the data contains. A hyphen inside a lane code and a hyphen as the separator collide, and nothing on screen shows it.
- Leaving
VLOOKUP's fourth argument off. Approximate match on an unsorted card is a wrong answer dressed as a right one. - Wrapping the result in
IFERROR. It hides the#N/Athat was the only honest cell on the row.
13Mini Exercises
- On the sample sheet, write
=COUNTIF($J$2:$J$11,A2)beside the first job. Expect 2, and expect 2 on all ten rows. - Put the single-key lookup
=XLOOKUP(A2,$J$2:$J$11,$L$2:$L$11)in a spare column and the multiplied form beside it. Expect 38.50 and 61.00 on the first row. - Total both rate columns against the pallets:
=SUMPRODUCT(C2:C11,E2:E11)and=SUMPRODUCT(D2:D11,E2:E11). Expect 5,590.15 and 7,377.65. - Build the joined key in M2:M11 and confirm
=XLOOKUP(A2&"|"&B2,$M$2:$M$11,$L$2:$L$11,,0)returns 61.00 on the first job. - Delete the GLW-ABD next-day row from a copy of the card and recalculate. Confirm the
COUNTIFScheck falls to 0 on that job while the single-key lookup goes on answering 36.90.
What to Take Away
A lookup answers the question you asked it, and "what is the rate for this lane" is not the question anybody meant. It was always "what is the rate for this lane at this service", and the second half was carried in a column the formula never read.
So the fault is never in the lookup. It is in a key that stopped being unique the day the card gained a second service, and in the fact that nothing in Excel marks the moment that happens.
One COUNTIFS beside the rate, answering 1 on every row, would have caught this in the week it started. Five rows, £1,787.50, on an invoice where every number was real and every formula was correct.