Topic 04
VLOOKUP
The first of the lookup family. VLOOKUP has been the go-to search function for decades — and understanding its limitations is just as important as understanding its syntax. Once you know its three problems, you'll understand exactly why XLOOKUP was built.
The Lookup Family
All lookup functions share one job: you know one thing, you want to find another. They differ in how they search, what they can return, and how flexible they are.
Classic · Topics 04 – 08
Classic
VLOOKUP
HLOOKUP
MATCH
INDEX
Modern · Topic 05
Modern
XLOOKUP
XMATCH
Spill · Topic 07
Spill
FILTER
The Core Rule — No Lookup Without a Known Value
Every lookup function — classic or modern — follows the same three-step logic. You must always start with something you already know.
Known Value
The piece of information you already have — an ID, a name, a code. Like providing your ID number to look up your bill.
→
Search Array
The column or range Excel searches through to find a match for your known value.
→
Return Value
The result you want back — a name, salary, department — from the same row where the match was found.
You can never search for nothing
A lookup without a known starting value is like calling a bank and saying "give me account information" without providing an account number. Every lookup formula needs a concrete value to search for first.
VLOOKUP — Three Known Problems
VLOOKUP works — but it was designed with constraints that cause real problems in practice. Knowing these problems is why you'll appreciate XLOOKUP immediately when you see it.
1
Searches only the leftmost column
VLOOKUP can only search in the first column of the table range you give it. If your known value is in column 2 or later — for example, searching by Name instead of ID — VLOOKUP simply cannot do it. You would have to restructure your data or use a different function.
2
Returns by column index number — not by name
To return a value, you have to tell VLOOKUP which column number to pick from (1, 2, 3…). This means you must count columns manually every time. If anyone inserts or deletes a column in your data, every VLOOKUP formula in that workbook silently returns the wrong value — with no error to warn you.
3
Approximate match is the default
The 4th argument of VLOOKUP —
range_lookup — defaults to TRUE (approximate match) if you leave it out. This means a forgotten argument can silently return the wrong row without any error. Always add 0 (or FALSE) as the 4th argument to force an exact match. This is the most common source of wrong VLOOKUP results.
VLOOKUP Syntax
= VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value
The known value you're searching for. Use an absolute reference (
$G$3) if you'll copy the formula — so it always points back to the same cell.
table_array
The table or range to search. Must include the search column as its first column. Using a named Table (e.g.
table1) is recommended — it auto-expands and is easier to read.
col_index_num
Which column to return — counted from the left edge of
table_array. Column 1 = search column, column 2 = next column, and so on. This is Problem 2 in action.
[range_lookup]
Always write
0 here. 0 = exact match (what you almost always want). 1 = approximate match (the dangerous default if you forget this argument). This is Problem 3.
Why lock lookup_value with $?
In the search sheet you'll write one VLOOKUP formula and copy it down for all 10 fields.
$G$3 keeps the reference locked on the dropdown cell as you copy — without it, the formula would shift to G4, G5… and return wrong results. This is the absolute reference from Topic 01 being used in a real scenario.
Training Exercise
Download lookup.xlsxThe file has two sheets
-
HR List — 741 staff records with 10 columns: ID, name, Building, Department, Phone, Status, Hire Date, Years, Benefits, Salary. Your first task is to convert this to a Table named
table1. - Search sheet — a dropdown cell at the top lets you select any staff ID. The sheet has two columns: vlookup and xlookup. Today you fill the vlookup column.
Step 1 — Create the Table
1
Go to the HR List sheet. Click anywhere in the data.
2
Press
Ctrl + T → confirm headers → OK.3
Table Design → Table Name → type
table1 → Enter.Step 2 — Fill the VLOOKUP Column
Go to the Search sheet. In the vlookup column, write a VLOOKUP formula for each field. All formulas search table1 using the selected ID from the dropdown cell — use an absolute reference so it stays locked when you copy down.
| Field | Col # | VLOOKUP Formula |
|---|---|---|
| ID | 1 | =VLOOKUP($G$3, table1, 1, 0) |
| name | 2 | =VLOOKUP($G$3, table1, 2, 0) |
| Building | 3 | =VLOOKUP($G$3, table1, 3, 0) |
| Department | 4 | =VLOOKUP($G$3, table1, 4, 0) |
| Phone | 5 | =VLOOKUP($G$3, table1, 5, 0) |
| Status | 6 | =VLOOKUP($G$3, table1, 6, 0) |
| Hire Date | 7 | =VLOOKUP($G$3, table1, 7, 0) |
| Years | 8 | =VLOOKUP($G$3, table1, 8, 0) |
| Benefits | 9 | =VLOOKUP($G$3, table1, 9, 0) |
| Salary | 10 | =VLOOKUP($G$3, table1, 10, 0) |
Problem 2 on display
Notice how every formula is identical except the column number —
1, 2, 3… 10. You counted those manually. Now imagine someone inserts a new column between Department and Phone in HR List. Every formula from col 5 onwards silently returns the wrong field — no error, no warning. This is exactly why column indexing is a problem. XLOOKUP solves this in the next topic.
Quick Exercise — Students File
A shorter exercise with fewer columns — specifically designed to make Problem 2 visible. The sheet shows the column numbers 3, 7, 8 above the result cells as a hint. Your job is to write the three VLOOKUP formulas that use those numbers.
Download students.xlsx- The lookup area is pre-built — University ID, Name, GPA, Fees with a student ID already entered. You only write the three formulas.
- The data table is already set up. Convert it to a table named
studentsif it isn't one already. - The numbers 3, 7, 8 shown on the sheet are the column index numbers. Count the columns yourself to verify — that's the point of this exercise.
| Field | Col # | VLOOKUP Formula |
|---|---|---|
| Name | 3 | =VLOOKUP(A6, students, 3, 0) |
| GPA | 7 | =VLOOKUP(A6, students, 7, 0) |
| Fees | 8 | =VLOOKUP(A6, students, 8, 0) |
Verify your cell reference
A6 is the University ID cell in the lookup area — check the actual cell address in your file before writing the formula. The exact cell depends on where the lookup table is positioned on the sheet.
You just counted to 7 and 8
To get GPA and Fees you had to count across 7 and 8 columns manually. What happens if someone adds a column between Address and City? GPA shifts from column 7 to 8, Fees from 8 to 9 — and your formulas silently return wrong values. In Topic 05 you'll rewrite these same three formulas with XLOOKUP and replace
3, 7, 8 with students[First Name], students[GPA], students[Fees] — no counting, no breaking.
What about HLOOKUP?
HLOOKUP is the horizontal version of VLOOKUP — same syntax, same three problems, but searches across a row instead of down a column. It's intentionally not covered here because XLOOKUP replaces both. You'll encounter it in the lab.