Docs tower · floor
How to use vlookup in excel, and the argument to always type
The mechanics of how to use vlookup in excel are four arguments long and Microsoft documents them precisely. Three of them are straightforward. The fourth is optional, most people leave it out, and leaving it out is how a spreadsheet returns confident wrong answers for months.
The plain-language version Microsoft gives is a good place to start: VLOOKUP says “what you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match”.
The four arguments, and which of them bites
Before the fourth argument, the structural rule that causes the other half of the failures. Microsoft states it as a tip and it is really a constraint: “the secret to VLOOKUP is to organize your data so that the value you look up is to the left of the return value.” VLOOKUP only looks rightwards from its first column. If the thing you know is to the right of the thing you want, no amount of adjusting the formula will help — the columns have to move, or the function has to change.
Now the argument that matters most.
Microsoft, on the fourth argument. Read the two halves together, because between them they explain
most of the wrong answers spreadsheets have ever produced. The default assumes your data is
sorted, and if it is not, it does not stop — it returns the closest value it found on the way. The two failure modes are not equally visible. An exact-match miss returns #N/A, which is
an error you can see and go and fix. An approximate-match miss returns a number, in the right
format, in the right cell, that is simply the wrong number. Nothing is highlighted. Nothing is
reported. It goes into the total. Which gives the single most useful habit in this entire subject: type the fourth argument every
time. Almost always “Approximate match — 1/TRUE assumes the first column in the table is sorted
either numerically or alphabetically, and will then search for the closest value. This is
the default method if you don’t specify one.”
FALSE. Leaving it off is not shorter, it is a different
function.
It is worth being concrete about how this goes wrong in practice, because it does not look like an error at any point. A lookup of employee IDs against an unsorted list, with the fourth argument omitted, will return a name for every row. Some of those names belong to the wrong employee. The sheet totals correctly, prints correctly, and is wrong. The people who find these usually find them a quarter later, when a number does not reconcile.
The second most common failure is the one the third argument causes, and it has the same character: it appears after the formula was working. col_index_num is a count inside the range, so a formula returning column 3 of A:E keeps pointing at the third column even after somebody inserts a new column at B — and the third column is now something else entirely. Named ranges and tables reduce this, and Microsoft notes you can use them instead of cell references.
And before any of this, the sentence Microsoft puts at the very top of its own VLOOKUP page:
“Try using the new XLOOKUP function, an improved version of VLOOKUP that works in any direction
and returns exact matches by default, making it easier and more convenient to use than its
predecessor.” That is the vendor recommending you not use the thing you came to read about. Both of the traps
on this floor — the left-hand column rule and the dangerous default — are ones XLOOKUP removes by
design. If your Excel has it, the honest answer to “how do I use VLOOKUP” is often “use the other
one”. VLOOKUP still matters because it is in millions of existing spreadsheets, and reading those
is a different job from writing new ones.
A practical note for the common case of looking things up across sheets, since Microsoft gives the form: =VLOOKUP(A2,'Client Details'!A:F,3,FALSE). Whole-column references like A:F work and are convenient; on very large workbooks they are also more work than a defined range, which is the trade to be aware of rather than a rule.
On the phrasing: how to use vlookup without naming the program is very nearly the same question, because Google Sheets has the function with the same shape — VLOOKUP(search_key, range, index, is_sorted) — and the same trap. Google’s own documentation is blunter than Microsoft’s about it: FALSE is “for an exact match, this is recommended”, while TRUE “is the default if is_sorted is unspecified”. Two companies, the same optional argument, the same advice to always supply it.
And if the answer comes back as #N/A, that is the honest failure rather than the dangerous one. It means the value was genuinely not found in the first column of the range — usually because of a stray space, a number stored as text, or a lookup value that is not in the first column at all. All three are checkable in a minute, and none of them are the formula being broken.
The floors below take it four ways: the function itself, the errors it produces, what to use instead, and the spreadsheet habits that make lookups reliable in the first place.
Where to start
Four ways in.
- “I need to write one now.”
- Start at the function
- “It returns #N/A.”
- Go to when it fails
- “My data is the wrong way round.”
- That is what to use instead
- “It worked and now it does not.”
- That is keeping it reliable
The function
Four arguments, one of them optional and dangerous. These floors take each in turn, in the order they cause trouble.
When it fails
Two kinds of failure with very different characters: one that shouts and one that does not. The second is the reason this floor exists.
What to use instead
Microsoft’s own first recommendation on its VLOOKUP page is a different function. This wing takes that seriously.
Keeping it reliable
Most lookup failures are data failures wearing a formula costume. These floors are about the sheet underneath.
What this tower will not do
It will not tell you to leave the fourth argument off. Microsoft documents what happens when you do, and it is the quietest way to be wrong in a spreadsheet.
It will not pretend VLOOKUP is the modern answer. Microsoft recommends XLOOKUP at the top of its own VLOOKUP page, and repeating that is more useful than defending the old function.
And it will not give you a formula to paste without the ranges. The ranges are the whole of it, and they are specific to your sheet. What holds instead is simple: the syntax, the constraint on the first column, the behaviour of the optional fourth argument and the recommendation to use XLOOKUP are all quoted from Microsoft’s own documentation.
Where this page got its facts
- Microsoft Support — VLOOKUP function (the syntax and all four arguments, the requirement that the lookup value sit in the first column, col_index_num counted inside the range, the statement that approximate match assumes sorted data and is the default, and the recommendation to try XLOOKUP instead) — support.microsoft.com, read 22 August 2026.
- Google Docs Editors Help — VLOOKUP (the same four arguments in Google Sheets, that is_sorted is optional, that FALSE is recommended for an exact match, and that TRUE is the default when it is left unspecified) — support.google.com, read 22 August 2026.
Written by Alberto Gulotta
Founder and editor of AI Tools Primer, writing from Palermo, Italy. Thirty-five years of taking computers apart, starting with a Commodore 64 — the long version is on the about page.
Something wrong on this page? Write to aitoolsprimer@gmail.com and it gets fixed.
Written on 22 August 2026.
Independence and limits
No affiliate links and no paid placements anywhere on this site. Nobody pays to appear here, and no company has seen this page before you did.
This is general information, not professional advice. Where a page touches money, health, safety or the law, it names its source and the date it was read — and your situation may still differ. See the privacy page and the cookie policy.