What Is VLOOKUP in Excel?
VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value from another column in the same row. It remains the most-tested lookup in university exams and Excel homework — even in 2026 — so every student must know it cold.
VLOOKUP Syntax Explained
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | What it means | Example |
|---|---|---|
lookup_value | The value to find (ID, name, code) | E2 or "Alice" |
table_array | The table to search (lookup column FIRST) | $A$2:$D$500 |
col_index_num | Which column to return (count from the left) | 3 = third column |
range_lookup | FALSE = exact, TRUE = approximate | Almost always FALSE |
5 Copy-Paste VLOOKUP Examples
1. Basic exact lookup (grades, prices, names)
=VLOOKUP(E2, $A$2:$D$500, 3, FALSE)
92 ← value returned from column C for the ID in E2
Finds the ID in E2 inside A2:A500 and returns the matching value from column C. Lock the table with $ so copying down doesn't shift it.
2. Friendly errors with IFERROR
=IFERROR(VLOOKUP(E2, $A$2:$D$500, 3, FALSE), "Not found")
Professors deduct marks for raw #N/A all over a sheet. Wrap every lookup in IFERROR.
3. Price lookup with dropdown validation
=VLOOKUP(G2, Products!$A$2:$C$200, 2, FALSE) * H2
G2 is a Data Validation dropdown of product names, H2 is quantity. Classic assignment pattern.
4. Grade bands with approximate match
=VLOOKUP(B2, $F$2:$G$6, 2, TRUE)
With TRUE, the first column must be sorted ascending (0, 50, 65, 75, 85 → F, D, C, B, A). This is the one legitimate use of approximate match.
5. Two-column key without helper chaos
VLOOKUP can't natively match two columns. For assignments, either add a helper column (=A2&"|"&B2) and look that up, or use INDEX+MATCH / XLOOKUP instead — and say so in your methodology note for bonus marks.
Fixing the 4 Errors Students Hit
- #N/A: value genuinely missing, extra spaces (
TRIMit), or numbers stored as text (convert withVALUEor Text-to-Columns). - #REF!: you deleted a column — recount
col_index_num. - Wrong value: you omitted FALSE, so Excel did an approximate match. Add
, FALSE. - First-row only works: missing
$locks — the table shifted as you copied down.
VLOOKUP vs XLOOKUP: What to Submit?
Show VLOOKUP (it proves you know the classic), then add one XLOOKUP column as the "robust alternative" with a one-line justification. Examiners reward that comparison. Read our full XLOOKUP vs VLOOKUP 2026 guide next.
Deadline tonight? Send the file — our experts return solved, explained VLOOKUP workbooks in hours.
