The trick hides in the "wrong" 4th argument — and in one little comma that changes everything.
Someone taught you one golden rule about VLOOKUP: always end it with FALSE. Exact match. Never TRUE. And that rule — as far as it goes — is fine. It's also quietly making your big workbooks crawl.
Here's the part nobody mentions: the exact-match VLOOKUP everyone swears by is the slow one. The "dangerous" approximate-match version — the one with TRUE — is the fast one. Much faster. We just have to tame it.
Let me show you something cool — like, really cool. But first, a tiny detour, because it turns out the door to the fast version has been sitting inside that 4th argument the whole time.
Quick hat trick: three ways to write "exact match"
You've been writing the formal, full-length version:
=VLOOKUP(value,range,col,FALSE)Watch how short we can make that last argument.
Move 1 — swap FALSE for 0. To Excel, FALSE is 0. They're the same value wearing different clothes. So this is identical, and shorter:
=VLOOKUP(value,range,col,0)Move 2 — leave it blank but keep the comma. An empty argument slot is read as 0, and 0 means FALSE. So this still does an exact match, with the fewest keystrokes of all:
=VLOOKUP(value,range,col,)That's the hat trick: FALSE → 0 → (blank). Three spellings, one behavior. Feel free to use whichever you like — I've grown fond of the lonely trailing comma.
But don't drop that comma — and this is the whole point of today's post. Delete the comma too, and you haven't written "FALSE by omission." You've written the function's default — which for VLOOKUP is
TRUE:=VLOOKUP(value,range,col) ← no comma = TRUE (approximate!) =VLOOKUP(value,range,col,) ← blank + comma = FALSE (exact)Two formulas that look like twins, doing the exact opposite thing. One character between them.
Ran it live, looking up
25 in a tiny table. FALSE, 0, and the blank comma all say #N/A — exact match, and 25 isn't there. Drop the comma and it cheerfully returns Banana. That's TRUE sneaking in through the back door.So file this away as a general habit: an empty argument (comma there, value gone) is read as 0/FALSE — a handy keystroke-saver across lots of functions. But fully dropping the comma hands control to the function's own default, and that default isn't always FALSE. VLOOKUP is living proof: its default is TRUE.
And that TRUE we keep tiptoeing around? That's the fast lane. Let's walk through it.
First, why FALSE is slow
When you write =VLOOKUP(id,Data,2,), Excel does the honest, plodding thing: it starts at the top of the lookup column and checks every single cell, one by one, until it finds your value. Row 1, row 2, row 3… all the way down.
On 100 rows, who cares. On 200,000 rows, with a few thousand of these formulas recalculating at once? Arrgh, what a slow, boring wait.
That's a linear search — "look at everything until you trip over the answer." Worst case, to find one value in a million rows, Excel makes a million comparisons. Now multiply that by every VLOOKUP on the sheet. That's your spinning cursor and your fan spinning up.
Why TRUE is fast: it stops looking at everything
Flip that last argument to TRUE (approximate match) and Excel switches to a completely different engine: a binary search.
Here's the plain-English version. Imagine finding "Nathan" in a paper phone book. You don't start at page 1 and read every name — that would be madness. You flip to the middle, land on "M," and think: too early, Nathan's after this. You throw away the entire first half. You flip to the middle of what's left, land on "R" — too far, back up. Throw away that half. A few flips and you're staring at Nathan.
That only works because the phone book is sorted. And it's brutally efficient: every flip throws away half of what's left.
- Guess a number between 1 and 100 by halving each time → you're done in 7 guesses, tops.
- Find one row in 1,000,000 → about 20 comparisons. Not a million. Twenty.
Put real numbers on it — worst-case comparisons to find one row:
| Rows | Linear (FALSE) | Binary (TRUE) |
|---|---|---|
| 1,000 | up to 1,000 | ~10 |
| 100,000 | up to 100,000 | ~17 |
| 1,000,000 | up to 1,000,000 | ~20 |
Read that table sideways: every time your data grows ten times bigger, linear search gets ten times slower — while binary search adds just three or four comparisons. One approach checks everything; the other barely looks. On big data you feel that gap the moment the sheet recalculates.
So why did your guru ban TRUE? Two reasons — and both are fixable:
- It needs the lookup column sorted ascending. No sort, no phone-book trick — you'll get garbage.
- It returns the closest match, not the exact one. Ask for an ID that doesn't exist and
TRUEcheerfully hands you the nearest neighbor instead of an error. Yikes.
Reason #2 is the scary one. But we can do better.
Seeing the "closest match" problem for real
Say your data is sorted like this — employee IDs in column A, salaries in column B:
| A (ID) | B (Salary) |
|---|---|
| 1001 | 50,000 |
| 1002 | 61,000 |
| 1005 | 58,000 |
| 1009 | 72,000 |
Now =VLOOKUP(1004,A:B,2,TRUE). There is no employee 1004. But TRUE doesn't shrug — it walks back to the largest ID that's not over 1004, which is 1002, and confidently returns 61,000 — no error, no warning, just a wrong salary handed over with a straight face.
That's the landmine. Here's how we defuse it while keeping the speed.
The 2-step "fast exact match" trick
The idea: use the fast binary search to find the row, then verify it's actually the row we asked for. If it is, return the value. If it isn't, say "not found." The speed of TRUE, the safety of FALSE.
Sort your table ascending by the lookup column, then:
=IF(VLOOKUP(A2,Data,1,TRUE)=A2,VLOOKUP(A2,Data,2,TRUE),"Not found")Reading it out loud, step by step:
VLOOKUP(A2,Data,1,TRUE)— do a fast approximate lookup that brings back the key column itself (column 1). This lands on the closest match, at binary-search speed.= A2— check: is that closest match actually the ID we asked for? If yes, the record truly exists.- If it exists →
VLOOKUP(A2,Data,2,TRUE)grabs the value we really wanted (column 2 — the value beside the key) — again by binary search. - If it doesn't → we return
"Not found"instead of a silently wrong number.
Run our 1004 example through that: step 1 finds 1002, step 2 asks "is 1002 = 1004?" — no — so we get "Not found" instead of a bogus salary. Exactly right.
The landmine and the fix in one shot. Bare
TRUE hands back 61,000 for ID 1004 — which doesn't exist. The verified version correctly says Not found for 1004, and still nails 58,000 for the real 1005.Two lookups instead of one, sure. But both are binary searches, so together they still leave a single linear exact-match VLOOKUP in the dust on large data. Put numbers on it one more time: on a million rows, our two binary searches cost about 40 comparisons total. The one "safe" linear VLOOKUP everyone recommends? Up to a million. Two of the fast thing still beat one of the slow thing, easily.
Now the punchline: turn the trap into the trick
Look at that formula again — every lookup in it ends in , TRUE:
=IF(VLOOKUP(A2,Data,1,TRUE) = A2,VLOOKUP(A2,Data,2,TRUE),"Not found")But we spent the whole top of this post learning that omitting the last argument is TRUE. And here — unlike the exact-match hat trick — TRUE is exactly what we're after. So drop it. Twice:
=IF(VLOOKUP(A2,Data,1) = A2,VLOOKUP(A2,Data,2),"Not found")Same binary search, same verify, same answer — just shorter. Remember the comma we warned you about? It was only a trap because there you wanted an exact match. Here we're playing with TRUE on purpose, so the same move turns into the goldmine: the default does the fast thing for free, and you type less to get it.
Proof — the short form (no
, TRUE) and the long form return the same 58,000 for a real ID and the same Not found for a missing one. Dropping the comma changed nothing but the length.If you'd rather keep TRUE written out so the next person knows it's deliberate, do — it's the same speed. But if you want the shortest thing that works, that's it.
One edge case, one wrap. If a lookup value can be smaller than your very first key, the inner VLOOKUP returns
#N/A. Catch it by wrapping the whole thing:=IFERROR( …the formula above…,"Not found"). Now every miss lands softly.
Prefer INDEX/MATCH? Same idea — and MATCH plays the very same game: its 3rd argument defaults to approximate (1), so you can drop it too. In modern Excel, compute the position once with LET:
=LET(pos,MATCH(A2,Keys),IF(INDEX(Keys,pos)=A2,INDEX(Vals,pos),"Not found"))MATCH(A2,Keys) — no 3rd argument — is the approximate (binary) match; the INDEX(Keys,pos) = A2 line is the same safety check. (Wrap it in IFERROR too, for the below-the-first-key case.)
Follow along —
Keys and Vals are just names for A3:A6 and B3:B6. One cell, one formula: 58,000 for the real 1005, Not found for the missing 1004.No LET yet — older Excel? Then don't compute MATCH twice; that doubles the search you just worked to speed up. Park the position once in a helper column and reuse it:
H2: =MATCH(A2,Keys)
I2: =IF(INDEX(Keys,H2) = A2,INDEX(Vals,H2),"Not found")One binary search, used twice. Hide column H when you're done and no one's the wiser.
This is the whole trick laid bare. Watch the
pos column — that's MATCH finding the row. Look up 1005 → pos 3 → 58,000. Look up 1004 → pos 2 (the nearest, 1002) → the verify catches the mismatch → Not found. Look up 1009 → pos 4 → 72,000. Copy the two formulas at the bottom into columns B and C and you're running it yourself.One honest catch
This trick still wants one rule: sort the lookup column ascending. But here's a subtlety worth getting right — and if you've tested it on unsorted data and watched it still work, you weren't imagining things.
The = A2 verify step means the formula only ever returns a value it has confirmed is an exact match. So even on unsorted data it will never hand you a wrong value — protection a bare approximate VLOOKUP simply doesn't have. What it can't promise is the other direction: finding every record that's actually there. On unsorted data the binary search still jumps and halves as if it were reading a sorted phone book, so it can stroll right past a key that exists and report "Not found" for a row sitting a few cells away.
That's why unsorted data can look fine: query a key the search happens to reach and you get the right answer; query one it can't reach and you get a quiet, unmarked "Not found". Whether you hit it depends entirely on how the rows are arranged and which keys you look up. Want to see it miss on purpose? Scramble some keys to 5, 1, 3, 2, 4 and look up 1 — the search can skip right over it. Sort the column, and 1 turns up instantly.
Proof of the catch — same formula, same key
1. On the scrambled list it reports Not found, even though 1 is sitting right there at position two. Sort the column and it finds v1 instantly. (The bare approximate version doesn't fare better — it returns #N/A.)So sort once and both worries disappear: every existing key is found, and found fast. If your data changes often, keep it sorted — an Excel Table plus a sort, or a sort step whenever you refresh. Sorting is a one-time cost; the fast lookups are forever.
And one trap that catches everybody at least once: keep your key column a single data type. If some IDs are real numbers while others are text that merely looks like a number — a stray leading space, a sneaky apostrophe, a column pasted in from a website — then both the sort and the binary search quietly go sideways. Excel files numbers and text into separate piles when it orders things, and the phone-book trick assumes one consistent order. If your fast lookup starts missing rows that clearly exist, this is almost always why. Clean the column to one type first (Text-to-Columns, or a VALUE/TEXT pass) and the matches snap back into place.
"But I already have XLOOKUP"
Good — then you get the same engine with a cleaner switch. XLOOKUP defaults to a linear exact match too (safe, but linear), so on a big sorted column, hand it the 6th argument, search_mode, set to 2:
=XLOOKUP(A2,Keys,Vals,"Not found",0,2)2 means "binary search, ascending." Same speed story, built right in, plus a clean "Not found" on a miss and no fragile column-number counting. On older Excel? The 2-step VLOOKUP above is your version of the exact same magic.
When is this actually worth it?
Let's be honest — not always. A quick gut-check:
- Small sheet (a few hundred rows), a handful of formulas? Don't bother. Plain
=VLOOKUP(…,)is instant and dead simple. Move on with your life. - Big table (tens of thousands of rows and up), and many lookup formulas recalculating together? This is exactly where the fast path earns its keep. This is the "why is my workbook frozen" zone.
- Data you can keep sorted? Green light. If it's naturally ordered by ID or date, you're basically already set up.
- Data that's messy and unsortable? Stick with exact
FALSE/0, or switch the sheet to XLOOKUP for readability. Correctness first, always.
Concluding Takeaway
"Always use FALSE" keeps you safe — it never returns a wrong answer. What it doesn't do is scale. On small sheets that costs you nothing; on big ones it quietly bleeds seconds on every recalculation, over and over.
So keep two things in your pocket:
- The hat trick —
FALSE,0, or a lonely trailing comma all mean "exact." But mind that comma: drop it entirely and you've flipped toTRUE. - The fast lane — on big, sorted data, let binary search find the row, then verify the hit. You get the speed of
TRUEwithout the risk that got it banned — and since the fast path wantsTRUE, you can drop the argument entirely for the shortest formula that works.
Sort your data, run the fast binary lookup, verify, and your laggy workbook wakes right up. Try it on your slowest sheet and watch the recalc — don't try to blink.
I hope this story useful to you!
