LOOKUP vs VLOOKUP: why the old one still shows up
Bare LOOKUP predates VLOOKUP and survives for two reasons: its vector form is pleasantly direct (lookup range and result range as separate arguments — like a primitive XLOOKUP), and its always-approximate matching powers classic tricks like finding the last non-blank cell. As a general lookup, though, it's a trap.
The differences at a glance
| LOOKUP | VLOOKUP | |
|---|---|---|
| Match mode | ALWAYS approximate — assumes sorted data, no exact option | Approximate by default, exact with FALSE |
| Unsorted data | Silently wrong answers | Correct with FALSE |
| Separate lookup/result ranges | Yes (vector form) — can look left | No — index into one table, rightward only |
| Famous trick | =LOOKUP(2,1/(rng<>""),rng) → last non-blank | — |
| Compatibility | Universal | Universal (both verified in every version we test) |
Which should you use?
- LOOKUP — Two situations only: genuinely sorted threshold data where you'd use approximate matching anyway, and the last-non-blank/last-match idioms built on its scan-to-the-end behavior.
- VLOOKUP — Ordinary exact lookups in older-version-compatible sheets (always passing FALSE). On modern versions, XLOOKUP supersedes both — it has LOOKUP's separate-ranges design plus an exact-match default.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute identically in every LibreOffice build and in the Google Sheets run we execute (desktop Excel per Microsoft's docs — we do not run desktop Excel) — LOOKUP's dangers are semantic (silent approximate matching), not compatibility. XLOOKUP, the modern replacement for both, needs Excel 2021+/365, current Sheets, or LibreOffice 24.8+ per our executed results. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 9 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| LOOKUP vector form (sorted thresholds) | =LOOKUP(D2,A2:A10,B2:B10) |
| Last non-blank cell (the LOOKUP idiom) | =LOOKUP(2,1/(A2:A100<>""),A2:A100) |
| VLOOKUP exact (the safe everyday form) | =VLOOKUP(D2,A2:B10,2,FALSE) |
Full per-version details on each function page: LOOKUP · VLOOKUP.