- The four parts of an Excel VLOOKUP formula
- Writing your first VLOOKUP in Excel step by step
- The VLOOKUP rule that catches everyone: the first column
- Exact match or approximate match in VLOOKUP
- Fixing common VLOOKUP errors in Excel
- XLOOKUP: the newer option Microsoft recommends
- VLOOKUP ideas for small business spreadsheets
- Frequently asked questions
To use VLOOKUP in Excel, type =VLOOKUP(what to find, the range to search, the column number to return, FALSE) and press Enter. The value you're searching for must sit in the first column of the range, and FALSE makes Excel look for an exact match.
VLOOKUP is the function that finally made spreadsheets click for me. You give it something to find, like a product code or a client's surname, tell it where to look, and it brings back a matching detail from the same row: a phone number, a stock level, a delivery date. No more scrolling and copying by hand.
It has four parts, and most VLOOKUP headaches come from just two of them. I'll explain each part in plain English, build one step by step, then cover the error messages and the newer function Microsoft now points people towards.
The four parts of an Excel VLOOKUP formula
Microsoft's help page gives the syntax as VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Translated:
| Part | What it means | Example |
|---|---|---|
| lookup_value | The thing you're searching for | A surname, an order code, or a cell holding it |
| table_array | The block of cells to search in | A1:B100 |
| col_index_num | Which column of that block holds the answer | 2 for the second column |
| range_lookup | Exact or approximate match | FALSE for an exact match |
Microsoft's own example is =VLOOKUP("Smith",A1:B100,2,FALSE). In words: find Smith in the first column of A1:B100, and return whatever sits in the second column of the same row, but only if there's an exact match.
Writing your first VLOOKUP in Excel step by step
Imagine a sheet with order codes in column A and customer names in column B, and a separate cell where you want to type a code and see the name.
- Type the code you want to find in an empty cell, say E2.
- Click the cell where the answer should appear, say F2.
- Type =VLOOKUP( and click E2. Type a comma.
- Drag across the table you're searching, starting with the column that holds the codes, for example A2:B10. Type a comma.
- Type 2, because the names are in the second column of that block. Type a comma.
- Type FALSE for an exact match, close the bracket and press Enter.
Type a different code in E2 and the name changes instantly. That's the moment most people get hooked.
The VLOOKUP rule that catches everyone: the first column
Microsoft is very specific about this. The value you're looking up must be in the first column of the range you select. If your range is B2:D7, the lookup value has to be in column B.
VLOOKUP can only look to the right. If the detail you want sits to the left of the column you're searching, VLOOKUP can't reach it. Either rearrange the columns or use the newer function I mention further down.
The column number counts from the left edge of your range, not from column A of the sheet. Microsoft says it starts at 1 for the left-most column of the range. So in a range starting at column C, column C is 1, D is 2, and so on.
Exact match or approximate match in VLOOKUP
The last part is optional, and that's the trap. If you leave it out, Microsoft says Excel assumes TRUE, an approximate match, and expects the first column to be sorted. On an unsorted list of names or codes, that can return a confidently wrong answer.
- FALSE (or 0): exact match. Use this for codes, names, email addresses and anything where close isn't good enough.
- TRUE: approximate match on sorted data, useful for bands and tiers, such as matching a score to a grade.
My rule of thumb: always type FALSE unless you know why you want TRUE.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Fixing common VLOOKUP errors in Excel
Microsoft's help lists the usual suspects. Here's what each one means:
| Error | Likely cause | What to try |
|---|---|---|
| #N/A | No exact match was found | Check spelling, spaces and that the value is in the first column |
| #REF! | The column number is bigger than the range | Widen the range or lower the column number |
| #NAME? | Text in the formula is missing its quotes | Put text lookups in double quotes, like "Smith" |
Microsoft's best-practice tips are worth following too:
- Lock the lookup range with an absolute reference before copying the formula down. Click the range inside the formula and press F4.
- Don't store numbers or dates as text, because a code saved as text won't match the same code saved as a number.
- Check for leading or trailing spaces, and for curly quote marks pasted in from other apps.
- Wildcards work with exact matches on text: ? stands for one character and * for any run of characters.
XLOOKUP: the newer option Microsoft recommends
On the VLOOKUP page itself, Microsoft suggests trying XLOOKUP, describing it as an improved version that works in any direction and returns exact matches by default. That fixes the two biggest VLOOKUP gotchas in one go: looking left, and forgetting FALSE.
If your version of Excel has it, XLOOKUP is worth learning. But VLOOKUP is still everywhere in shared templates and older files, so understanding it means you can fix other people's sheets as well as build your own.
VLOOKUP ideas for small business spreadsheets
- Pull a customer's email onto an order sheet by typing their account code.
- Bring stock levels from a stock list onto a reorder sheet.
- Match a product code to its description on a packing list.
- Look up a client's preferred contact method on a follow-up tracker.
Once your lookups are working, COUNTIF for counting entries that meet a rule is the natural next function to learn, and a pivot table will summarise the combined data without any formulas at all.
Frequently asked questions
How do I use VLOOKUP in Excel for an exact match?
Put FALSE as the last part of the formula, for example =VLOOKUP("Smith",A1:B100,2,FALSE). Microsoft says FALSE or 0 searches for the exact value.
Why does my VLOOKUP return #N/A?
Microsoft says #N/A appears when an exact value isn't found. Check for extra spaces, numbers stored as text, and that the value sits in the first column of your range.
Can VLOOKUP look to the left?
No. The value you're searching for must be in the first column of the range, so VLOOKUP only returns columns to its right. XLOOKUP works in any direction.
What does the column number mean in VLOOKUP?
It's the position of the answer column within your selected range, starting at 1 for the left-most column of that range, not of the whole sheet.
Should I use VLOOKUP or XLOOKUP?
Microsoft suggests trying XLOOKUP, which works in any direction and returns exact matches by default. VLOOKUP still works and appears in many existing files.
