Fernside Software's renewal letters take each contract value from the CRM by account ID. Tamsin Kitchens was quoted £22,500 instead of £64,800, signed it, and took £42,300 a year off the revenue line.
| Account ID | Account | Letter quoted | CRM value |
|---|---|---|---|
| 0015g00000Lm2Xp | Corran Foods | £22,500 | £22,500 |
| 0015g00000Lm2XP | Tamsin Kitchens | £22,500 | £64,800 |
Scope.
EXACT,INDEX,MATCH,SUMPRODUCTandCOUNTIFwork in every version of Excel.XLOOKUPneeds Excel 2021 or Microsoft 365.Before Excel 365,
INDEXwithMATCH(TRUE,EXACT(...),0)must be confirmed with Ctrl+Shift+Enter. TheSUMPRODUCTversion in section 5 never needs it.
01VLOOKUP Ignores Case, So Two IDs Became One
The CRM issues 15-character account IDs, and upper and lower case are different characters in them. Lm2Xp and Lm2XP are two customers. Excel's lookup functions do not agree.
The CRM export sits on a sheet called CRM, IDs in A2:A9 and contract values in C2:C9. The renewals sheet looked each ID up with, in C2:
=VLOOKUP(A2,CRM!$A$2:$C$9,3,FALSE)
Fernside Software Renewals, Contract Value by Account ID
Eight accounts in A2:F9 of the Renewals sheet. A is the 15-character CRM account ID, which is case-sensitive, and B the account name. The CRM export sits on a sheet called CRM, with the same IDs in A2:A9 and the contract value in C2:C9, £234,000 in all. C is what VLOOKUP returned, D the case-sensitive lookup with EXACT, E the COUNTIF of each ID in the CRM sheet and F the count with EXACT.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
FALSE asks for an exact match, and it got one, in Excel's sense of exact. Exact match in VLOOKUP means the same letters, in any case. For Tamsin Kitchens in row 6 it found Lm2Xp on row 3 first and stopped there.
Scenario: Put the eight IDs and values on a sheet called CRM, with the same IDs in A2:A9 of a second sheet. Enter =VLOOKUP(A6,CRM!$A$2:$C$9,3,FALSE) and check that it returns 22,500 for Lm2XP.
02The First Match Wins, in Both Directions
A case-blind lookup returns whichever of the pair comes first in the table. The second account of each pair gets the first one's value, whether that is bigger or smaller.
| Account | VLOOKUP | CRM value | Error |
|---|---|---|---|
| Pellow & Daughters | £48,000 | £9,600 | +£38,400 |
| Tamsin Kitchens | £22,500 | £64,800 | −£42,300 |
| All eight accounts | £230,100 | £234,000 | −£3,900 |
The two errors nearly cancel. A renewal total £3,900 under the CRM looks like rounding or a late change, so nobody went looking. The per-account letters were where the damage was.
Pellow & Daughters received a letter at £48,000, five times its contract, and phoned in. That call was the first sign anything was wrong.
Scenario: Add =SUM(C2:C9) under the VLOOKUP column and =SUM(CRM!C2:C9) beside it. Check that the gap is only 3,900 although two accounts are wrong by more than 38,000 each.
03EXACT Is the One Comparison That Sees Case
Every ordinary text comparison in Excel folds case. =A2=A4 compares KqRtA with KqRta and returns TRUE. So do MATCH, XLOOKUP, COUNTIF, SUMIFS and = inside IF.
EXACT compares two strings character for character, case included:
=A2=A4 → TRUE
=EXACT(A2,A4) → FALSE
=EXACT(A2,A2) → TRUE
Given a range instead of one cell, EXACT(CRM!$A$2:$A$9,A2) returns eight TRUE/FALSE values, one per CRM row. In a clean CRM, exactly one is TRUE when the ID exists. Every fix below looks for that TRUE.
Scenario: In an empty cell, enter =A2=A4 and then =EXACT(A2,A4). Check that the first returns TRUE and the second FALSE for KqRtA and KqRta.
04Look Up TRUE in the EXACT Array
Instead of looking up the ID, look up TRUE in the list of EXACT results. The position of the only TRUE is the row with the right case.
With XLOOKUP, in D2:
=XLOOKUP(TRUE,EXACT(CRM!$A$2:$A$9,A2),CRM!$C$2:$C$9,"Not found")
The short form, =XLOOKUP(TRUE,EXACT(CRM!$A$2:$A$9,A2),CRM!$C$2:$C$9), returns #N/A for a missing ID instead of the text. Both return 64,800 for Tamsin Kitchens.
In older versions, INDEX and MATCH do the same job:
=INDEX(CRM!$C$2:$C$9,MATCH(TRUE,EXACT(CRM!$A$2:$A$9,A2),0))
The 0 in MATCH is not optional: without it MATCH binary-searches a list of TRUE and FALSE values and lands anywhere.
| Account | VLOOKUP | EXACT lookup |
|---|---|---|
| Brightline Studios | £48,000 | £48,000 |
| Pellow & Daughters | £48,000 | £9,600 |
| Tamsin Kitchens | £22,500 | £64,800 |
Scenario: Enter =XLOOKUP(TRUE,EXACT(CRM!$A$2:$A$9,A2),CRM!$C$2:$C$9) in D2 and fill it to D9. Check that D4 returns 9,600 and D6 returns 64,800.
05SUMPRODUCT Works in Every Version
SUMPRODUCT handles arrays natively, so it needs no Ctrl+Shift+Enter in any Excel. Multiply the EXACT array by the values and add up:
=SUMPRODUCT(--EXACT(CRM!$A$2:$A$9,A2),CRM!$C$2:$C$9)
The -- turns TRUE and FALSE into 1 and 0. Only the row with the right case keeps its value; the rest become zero.
It has two habits a lookup does not. A missing ID returns 0, not an error, and an ID that appears twice returns the two values added together.
So pair it with a count, =SUMPRODUCT(--EXACT(CRM!$A$2:$A$9,A2)), and trust the value only where that count is 1. It works for numbers only; for a name or a date use INDEX and MATCH.
Scenario: Enter =SUMPRODUCT(--EXACT(CRM!$A$2:$A$9,A2),CRM!$C$2:$C$9) beside the XLOOKUP column. Change A6 to an ID that is not in the CRM and check that it returns 0.
06Find Every ID That Collides
The renewals run needed a warning before the letters went out. COUNTIF ignores case and EXACT does not, so comparing the two counts finds every ID that has a twin.
In E2 and F2, filled to row 9:
E2: =COUNTIF(CRM!$A$2:$A$9,A2)
F2: =SUMPRODUCT(--EXACT(CRM!$A$2:$A$9,A2))
| Account ID | COUNTIF | EXACT count | Collision |
|---|---|---|---|
| 0015g00000KqRtA | 2 | 1 | Yes |
| 0015g00000Lm2Xp | 2 | 1 | Yes |
| 0015g00000KqRta | 2 | 1 | Yes |
| 0015g00000Nw7Hc | 1 | 1 | No |
Four of the eight IDs collide. Brightline Studios is one of them, although VLOOKUP returned its own value: it was right only because it came first.
For one cell that answers the whole CRM sheet, =SUMPRODUCT(--(COUNTIF(CRM!$A$2:$A$9,CRM!$A$2:$A$9)>1)) returns 4. Anything above 0 means a case-blind lookup is unsafe on this table.
Scenario: Fill =COUNTIF(CRM!$A$2:$A$9,A2) down E2:E9 and =SUMPRODUCT(--EXACT(CRM!$A$2:$A$9,A2)) down F2:F9. Check that four rows show 2 in E and 1 in F.
07The Rest of Excel Folds Case Too
The lookup is not the only place the two accounts merge. Most of Excel treats Lm2Xp and Lm2XP as one value:
| Tool | What it does with the pair |
|---|---|
| Remove Duplicates | Deletes the second row |
| Pivot table | One row, values added |
UNIQUE | Returns one of them |
SUMIFS, COUNTIFS | Adds both into one total |
| Power Query merge | Keeps them apart |
Power Query is the exception: its merge compares text case-sensitively by default. A pivot table built from the CRM export would have reported one account worth £87,300.
If the source system can export a key that does not depend on case, ask for it. Otherwise keep EXACT in every formula that touches the ID.
Version note. No version of Excel has a case-sensitive
UNIQUE. To list distinct IDs with case, keep the rows where=SUMPRODUCT(--EXACT($A$2:A2,A2))returns 1, or do it in Power Query.
Scenario: Copy the CRM sheet, select A1:C9 and run Data > Remove Duplicates on column A. Check that Excel reports two duplicates removed and that Tamsin Kitchens is gone.
Try it in the grid08Five Traps
- Trusting FALSE in VLOOKUP. It means exact letters, not exact case. Lm2Xp and Lm2XP match.
- Trusting the total. Opposite errors cancel: £80,700 of mistakes moved the total by £3,900.
MATCHwithout the 0.MATCH(TRUE,EXACT(...))with no match type does a binary search and returns a wrong row.SUMPRODUCTon a missing ID. It returns 0, which reads as a real contract value. Check theEXACTcount.- Cleaning the IDs with
UPPER. It makes every lookup agree by turning two accounts into one permanently.
09Four Exercises
- Reproduce it. Build the CRM sheet and the VLOOKUP column, and confirm that Pellow & Daughters returns 48,000.
- Compare. Put
=A2=A4and=EXACT(A2,A4)side by side, and confirm TRUE and FALSE. - Fix it. Replace VLOOKUP with the
XLOOKUPandEXACTformula, and confirm the column totals 234,000. - Guard it. Add the
COUNTIFandEXACTcounts, and confirm four collisions with the one-cellSUMPRODUCTcheck.
What to Take Away
The CRM was right and the formula had no typo. Excel's lookups compare text without case, so two accounts that differ by one capital letter shared one contract value, and a letter went out at a third of the price.
Where IDs are case-sensitive, look up TRUE in an EXACT array, and count each ID with both COUNTIF and EXACT before anything leaves the building.