You have a spreadsheet with thousands of rows and you need to find a specific piece of data — a price, a name, an ID — based on a value you already know. Doing it manually would take hours. The VLOOKUP function does it in seconds. It is one of the most widely used functions in Excel, present in almost every real-world spreadsheet, and once you understand its four arguments, you can use it confidently in any situation. This step-by-step guide explains each argument, walks through six practical examples, and covers every common error you are likely to encounter.
VLOOKUP Syntax and What Each Argument Does
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Required? | What it means |
|---|---|---|
| lookup_value | Required | The value you want to find — a cell reference, text in quotes, or a number. This value must exist in the first column of your table_array. |
| table_array | Required | The range of cells containing your data. The leftmost column is where VLOOKUP searches. Lock this range with $ signs when copying the formula down: $A$2:$D$100. |
| col_index_num | Required | The column number within table_array to return from. Column 1 is the leftmost column of your range (the search column). Column 2 is the next one, and so on. |
| range_lookup | Optional | Use FALSE (or 0) for exact match — almost always what you want. Use TRUE for approximate match, which requires a sorted lookup column and is mainly used for banded ranges like tax brackets. |
Always use FALSE: VLOOKUP defaults to TRUE (approximate match) if you omit the fourth argument. On unsorted data this returns wrong values with no error. Always type
FALSE or 0 as the fourth argument unless you specifically need approximate match for a sorted range.
How to Write a VLOOKUP — 4 Steps
-
1Identify the lookup valueWhat value do you already know? It might be a product code in cell E2, a name, or an order number. This goes as the first argument:
=VLOOKUP(E2, -
2Select your data tableHighlight the range containing your data. The lookup value must be in the first column of this range. Lock it with dollar signs:
=VLOOKUP(E2, $A$2:$D$100, -
3Count the return columnCount from the left edge of your range to the column holding the data you want. If your range starts at column A and the return value is in column C, that is column 3:
=VLOOKUP(E2, $A$2:$D$100, 3, -
4Add FALSE for exact matchType FALSE and close the bracket:
=VLOOKUP(E2, $A$2:$D$100, 3, FALSE)— then press Enter. Done.
Example 1: Look Up a Price by Product Name
The most fundamental VLOOKUP use — search a product name in a price list and return the corresponding price. This is the scenario most beginners encounter first.
1
Find the price of a product from a catalogue
#
A — Product
B — Category
C — Price
2
Laptop
Electronics
$1,200
3
Monitor
Electronics
$320
4
Keyboard
Accessories
$85
5
Mouse
Accessories
$45
Lookup value "Monitor" is in cell E2. Data is in A2:C5.
Price is in column 3 (C). FALSE = exact match.
=VLOOKUP(E2, $A$2:$C$5, 3, FALSE)
Result: $320
With hardcoded text instead of a cell reference:
=VLOOKUP("Monitor", $A$2:$C$5, 3, FALSE)
VLOOKUP finds "Monitor" in column A (row 3), then moves to column 3 (Price) of the same row and returns $320.
Lock your table range: When you copy the formula down to other rows, the table_array must stay fixed. Always use
$A$2:$C$5 (with dollar signs) — not A2:C5 — so the range does not shift as you copy.
Example 2: Look Up Employee Details by ID
A very common HR use case — find an employee's department and salary by entering their ID number. Notice how changing the col_index_num lets you pull different columns from the same search.
2
Return name, department, or salary from an employee ID
#
A — EmpID
B — Name
C — Dept
D — Salary
2
E-1001
Alice Chen
Sales
$62,000
3
E-1002
Ben Khalid
Engineering
$78,000
4
E-1003
Chloe Park
HR
$55,000
Search for ID "E-1002" (in cell F2) and return different columns:
Name (column 2):
=VLOOKUP(F2, $A$2:$D$4, 2, FALSE)
Result: "Ben Khalid"
Department (column 3):
=VLOOKUP(F2, $A$2:$D$4, 3, FALSE)
Result: "Engineering"
Salary (column 4):
=VLOOKUP(F2, $A$2:$D$4, 4, FALSE)
Result: $78,000
Same formula, different columns: The only thing that changes between these three formulas is the col_index_num — 2, 3, or 4. Everything else stays identical. This is why locking the table_array with
$ signs is so important — you can reuse the same range across multiple formulas.
Example 3: VLOOKUP Across Two Sheets
In most real projects, your lookup table lives on a different sheet from your working data. VLOOKUP handles this easily — you just prefix the range with the sheet name. Excel fills this in automatically when you click the range on another sheet while building the formula.
3
Pull a price from a "PriceList" sheet into your orders sheet
Table lives on a sheet named "PriceList". Reference it with Sheet!Range:
=VLOOKUP(A2, PriceList!$A$2:$C$200, 3, FALSE)
Sheet name with a space — wrap in single quotes:
=VLOOKUP(A2, 'Price List'!$A$2:$C$200, 3, FALSE)
Using a named range (cleaner and easier to maintain):
=VLOOKUP(A2, ProductPrices, 3, FALSE)
Build it by clicking, not typing: Start typing
=VLOOKUP(A2, then click the PriceList sheet tab and select your range. Excel writes the SheetName!$A$2:$C$200 reference automatically. This avoids typos and ensures the range is correct.
Example 4: Handle #N/A Errors with IFERROR
When VLOOKUP cannot find the lookup value, it returns a #N/A error. In a shared workbook or a dashboard, this is not user-friendly. Wrap VLOOKUP in IFERROR to show a custom message — or a blank — instead.
4
Show "Not found" instead of a #N/A error
Search value
Without IFERROR
With IFERROR
Monitor (exists)
$320
$320
Tablet (missing)
#N/A
Not found
(empty cell)
#N/A
(blank)
Show "Not found" when the value does not exist:
=IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), "Not found")
Return a blank cell instead of an error:
=IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), "")
Return zero for numeric columns (useful in SUM formulas):
=IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), 0)
Example 5: Approximate Match for Grade Banding
The TRUE (approximate match) mode is useful when your lookup values fall within ranges rather than matching exact values. Classic uses include tax brackets, discount tiers, and grade scales. The lookup table must be sorted ascending by the first column for this to work correctly.
5
Assign a grade from a sorted score banding table
A — Min Score
B — Grade
0
F
60
D
70
C
80
B
90
A
TRUE = approximate match. Score 85 returns "B" (largest value <= 85 is 80).
=VLOOKUP(D2, $A$2:$B$6, 2, TRUE)
Score 85 -> finds 80 (largest value <= 85) -> returns "B"
Score 94 -> finds 90 -> returns "A"
Score 57 -> finds 0 -> returns "F"
Sort ascending — mandatory: Approximate match only works correctly if the first column is sorted in ascending order. On unsorted data it returns random wrong values. Note that with TRUE mode you do not need every value — VLOOKUP returns the largest value that is less than or equal to the lookup value.
Example 6: Wildcard Partial Match
VLOOKUP supports wildcards in the lookup value — * for any sequence of characters and ? for a single character. This lets you find a row when you only know part of the lookup value, such as a partial company name or an incomplete product code.
6
Find a company record using a partial name match
Search term in F2 = "Contoso". Match against "Contoso Holdings" in column A:
Starts with (add * at the end):
=VLOOKUP(F2&"*", $A$2:$C$100, 2, FALSE)
Contains (add * on both sides):
=VLOOKUP("*"&F2&"*", $A$2:$C$100, 2, FALSE)
Hardcoded partial search:
=VLOOKUP("Cont*", $A$2:$C$100, 2, FALSE)
Wildcard caution: VLOOKUP returns the first match. If multiple rows contain your partial search term, only the first one is returned. Make sure your search term is specific enough to identify a unique record.
Common VLOOKUP Errors and How to Fix Them
| Error | Most common cause | Fix |
|---|---|---|
| #N/A | Lookup value not found in the first column of the table | Check for trailing spaces, data type mismatch (text vs number), or the value genuinely not existing. Wrap with IFERROR for a user-friendly fallback. |
| #REF! | col_index_num is larger than the number of columns in table_array | Count your columns again. If table_array is A:C (3 columns), the maximum col_index_num is 3. |
| #VALUE! | col_index_num is less than 1, or a non-numeric value was used | col_index_num must be a positive integer. Column 1 is the leftmost — there is no column 0 or -1. |
| Wrong value returned | Fourth argument omitted or set to TRUE on unsorted data | Always add FALSE as the fourth argument for exact match lookups. |
| Formula breaks after column insert | col_index_num is hardcoded and shifts when columns are added | Replace the hardcoded number with MATCH("Header", $A$1:$D$1, 0) to make the column reference dynamic. |
| #N/A on numbers | Numbers stored as text in the lookup column (or vice versa) | Check for a small green triangle in cell corners — that means numbers stored as text. Convert with Data > Text to Columns, or wrap lookup_value with VALUE(). |
Frequently Asked Questions
-
What does VLOOKUP do in Excel?VLOOKUP searches for a value in the first column of a table and returns a corresponding value from another column in the same row. It stands for "Vertical Lookup" — it always searches vertically down a column. You provide what to look for, where to look, which column to return from, and whether to use exact or approximate matching.
-
Why does VLOOKUP always need FALSE at the end?The fourth argument controls the match type. Without it, VLOOKUP defaults to TRUE — approximate match — which requires your lookup column to be sorted ascending. On unsorted data it silently returns wrong values. Always add FALSE (or 0) for exact match unless you are deliberately using a sorted banding table for range lookups.
-
Why must the lookup value be in the first column?VLOOKUP is designed to search the leftmost column of the table_array and return values from columns to its right. It cannot look left. If your search column is not the first column in your range, either rearrange your data, use INDEX MATCH instead (which has no direction restriction), or use XLOOKUP if you are on Excel 365 or 2021.
-
How do I use VLOOKUP across two different sheets?Add the sheet name followed by an exclamation mark before the range: =VLOOKUP(A2, Sheet2!$A$2:$C$100, 3, FALSE). If the sheet name contains spaces, wrap it in single quotes: =VLOOKUP(A2, 'Price List'!$A$2:$C$100, 3, FALSE). The easiest way to get this right is to start typing the formula on your working sheet, then click the other sheet tab and select the range — Excel writes the reference for you.
-
What is the difference between VLOOKUP and XLOOKUP?XLOOKUP (available in Excel 365 and 2021) is the modern replacement for VLOOKUP. It can look left, does not break when columns are inserted, defaults to exact match, has built-in error handling, and can return multiple columns at once. VLOOKUP works in all Excel versions including 2016 and 2019. If you are on Excel 365 or 2021, prefer XLOOKUP for new formulas; keep VLOOKUP when compatibility with older Excel versions is required.
-
Why does VLOOKUP return a wrong value instead of an error?This almost always means the fourth argument is TRUE (or omitted), enabling approximate match. In this mode, VLOOKUP returns the largest value that is less than or equal to the lookup value — which produces a plausible-looking but wrong answer on unsorted data. Add FALSE as the fourth argument to force exact match behaviour.