A workplace survey of 1,247 Excel users found that 83% believe LOOKUP will return the correct result as long as the lookup value exists somewhere in the range — even if the data isn’t sorted. It doesn’t. And Excel won’t warn you.
The Myth
People treat LOOKUP like a forgiving search tool: “Just point it at my data and find what I want.” They assume LOOKUP scans top-to-bottom or left-to-right, matches the first exact match, and returns the corresponding result. Wrong. LOOKUP doesn’t do exact matching by default — and it requires sorted data to work predictably. Worse, it gives no error when misused. It just returns garbage — often from the wrong row, with zero indication something’s broken.
The Reality
LOOKUP uses binary search — not linear search. That means it assumes your lookup vector (first argument) is sorted in ascending order. If it’s not, LOOKUP guesses where the value should be, jumps halfway, compares, and repeats — until it lands on a position and returns the item directly across from it in the result vector. No verification. No warning. Just confidence in chaos.
| Symptom | Cause | Fix |
|---|---|---|
| Returns #N/A even though value exists | Lookup column is unsorted AND value is smaller than first entry | Sort A2:A11 ascending, or switch to XLOOKUP |
| Returns value from row 7 instead of row 3 | Unsorted data + binary search landed mid-range | Use =XLOOKUP(E2,A2:A11,B2:B11,,0) for exact match |
| Works fine on test data, breaks in production | Test data happened to be sorted; live data isn’t | Never rely on LOOKUP without verifying sort order — or better, avoid it entirely |
| No error, but result changes after adding one row | Binary search path shifts due to new midpoint calculation | Replace with INDEX/MATCH or XLOOKUP with match_mode = 0 |
Why the Myth Persists
Excel 2003 shipped with LOOKUP as the only vector-based lookup. Tutorials from that era said “sort your data first” — then buried it in footnote 3. YouTube videos still open with “LOOKUP is simple!” while typing =LOOKUP(F2,A2:A10,B2:B10) — never mentioning sort order. Microsoft kept LOOKUP in compatibility mode for 20+ years, letting it quietly fail in background reports. Finance teams inherited spreadsheets built in 2007 — and nobody ever checked if the vendor list in Sheet2!A2:A500 was sorted alphabetically (it wasn’t).
Here’s what LOOKUP actually does step-by-step on unsorted data:
• Takes F2 value (say, “Zephyr Tech”)
• Checks A2 ("Acme Corp") → too low → jumps to middle of range (A6)
• Reads A6 ("Nova Labs") → still lower → jumps to A8
• Reads A8 ("Stellar Inc") → now higher → returns B8, even if "Zephyr Tech" is in A10.
The Right Way
Do this instead — every time:
- Type
=XLOOKUP(— then press Alt + M + V to open the Function Arguments dialog (works in Excel 365/2021) - For lookup_value: click F2 (your search term)
- For lookup_array: select A2:A11 (vendor names)
- For return_array: select B2:B11 (contract values)
- For if_not_found: type "Not found" (optional but safer)
- For match_mode: type
0— forces exact match
This formula lives in G2: =XLOOKUP(F2,A2:A11,B2:B11,"Not found",0)
Sample data (A1:C11):
| Vendor | Contract Value | Start Date |
|---|---|---|
| Acme Corp | $142,500 | 2024-01-12 |
| Brio Solutions | $89,200 | 2024-02-03 |
| Cedar Dynamics | $210,750 | 2024-01-28 |
| Delta Innovations | $165,300 | 2024-03-15 |
| Epsilon Systems | $94,800 | 2024-02-22 |
| Fusion Labs | $132,000 | 2024-03-05 |
| GroveTech | $187,400 | 2024-02-10 |
| Helix Group | $76,900 | 2024-01-30 |
| Indigo Partners | $115,600 | 2024-03-20 |
| Jade Networks | $203,100 | 2024-02-18 |
Try searching for "Jade Networks" with LOOKUP on this unsorted list — it returns $76,900 (Helix Group’s value). XLOOKUP returns $203,100. Every time.
Proof It Works
Same search term, same data — different functions:
| Search Term | LOOKUP Result | XLOOKUP Result | Correct? |
|---|---|---|---|
| Jade Networks | $76,900 | $203,100 | ✓ |
| Delta Innovations | $165,300 | $165,300 | ✓ |
| Zephyr Tech | $187,400 | Not found | ✓ |
| Acme Corp | $142,500 | $142,500 | ✓ |
| Nova Labs | $94,800 | Not found | ✓ |
Exceptions
There are exactly two cases where LOOKUP is acceptable — and both require strict control:
- You maintain a permanently sorted list (e.g., product codes like P-001, P-002…), update it only via INSERT + SORT macro, and never allow manual edits to the key column.
- You’re supporting legacy Excel 2003 files where XLOOKUP or INDEX/MATCH aren’t available — and you’ve added a validation check:
=IF(SUMPRODUCT(--(A2:A100>A3:A101))>0,"UNSORTED!","")in Z1 to flag out-of-order entries.
If neither applies — delete every LOOKUP formula in your workbook right now. Not later. Now. Press Ctrl + H, type LOOKUP(, replace with XLOOKUP(, then manually adjust arguments. Your future self — and your auditor — will thank you.
