VLOOKUP looks for a value in the first column of a table and returns a value from the same row. The version worth learning is =VLOOKUP(lookup_value, table_array, col_index_num, FALSE): it finds an exact match or returns #N/A. Give it the ID E-104 in a staff list and it hands back that person's salary.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_valueis the thing you are looking for, such as a product code or an employee ID.table_arrayis the block of cells to search. The first column of that block is the only one searched.col_index_numcounts columns inside the block, starting at 1 for the looked-up column itself.range_lookupisFALSEfor an exact match.TRUEis the approximate form and needs a sorted column.
Two facts explain most of the confusion. VLOOKUP only searches the leftmost column of the block you pass it, so it can never look left. And the column number is counted inside the block, not on the sheet, so 2 means the second column of table_array.
Examples
Example 1: an ID to a salary
The directory holds IDs in column A, names in B and salaries in C, with rows 1 to 6 in use. The ID to find sits in D2.
=VLOOKUP(D2, A2:C6, 3, FALSE)
VLOOKUP reads D2, walks down A2:A6 until it finds E-104, then moves three columns right inside the block and returns 54000 from C5. If the ID is not there, the result is #N/A and no salary is shown. That refusal is the point of the exact match: a wrong salary is worse than an error.
Example 2: copied down a column
Drag the formula down and the lookup value moves with it, from D2 to D3, D4 and so on. That is what you want. The table should not move, so lock it:
=VLOOKUP(D2, $A$2:$C$6, 3, FALSE)
The dollar signs hold the block still while the lookup value walks down the column. Without them the block slides to A3:C7, A4:C8, and rows near the bottom of the list start returning #N/A for no visible reason. This is the single most common VLOOKUP bug in shared workbooks.
Example 3: a clean label instead of an error
A missing ID is not always a mistake. Sometimes the row simply has no match yet, and #N/A sprayed across a report looks broken. Wrap the formula:
=IFERROR(VLOOKUP(D2, $A$2:$C$6, 3, FALSE), "not found")
IFERROR catches the error and prints whatever text you give it. Use it when a blank or a note is the honest answer, and skip it when a missing match is a fact someone needs to see. Hiding #N/A under an empty string is how a broken join survives a whole quarter unnoticed.
What goes wrong
| Error | What it means | The fix |
|---|---|---|
#N/A | The value is not in the first column of the block | Check for trailing spaces and text stored as numbers |
#REF! | The column number is wider than the block | Count again from the block's first column |
#VALUE! | The block is not a range, or the column number is zero | Pass a real range such as A2:C6 |
| Wrong value, no error | Approximate match with an unsorted column | Put FALSE in the fourth argument |
The #N/A case is usually the text being there and still not matching. Typed or imported data carries invisible spaces, so E-104 and E-104 are two different values. TRIM cleans that up, and EXACT will show you the difference.
Numbers exported as text cause the same problem in reverse: the ID looks like 10104, but it is a string, and a numeric lookup never finds it.
Sorted columns are the other trap. TRUE is the default when the fourth argument is left out, and it assumes the first column is sorted ascending. On an unsorted list it returns the nearest value below the one you asked for.
The answer is simply wrong, and the cell shows a salary as usual. If a VLOOKUP ever returns a value from the wrong row, check for a missing fourth argument before anything else.
Alternatives
=INDEX(C2:C6, MATCH(A2, A2:A6, 0)) returns the same value with two functions instead of one. It exists for three reasons: it can look to the left, it survives a column being inserted, and it works in every version of Excel. XLOOKUP, added in Excel 2021 and Microsoft 365, does all of that in one function, so =XLOOKUP(A2, A2:A6, C2:C6) is what to type on a new sheet.
The older pair is not legacy in a file you send out. If the person opening it has Excel 2019, XLOOKUP returns #NAME? and the lookup column goes blank, so VLOOKUP is still the safest formula to hand to a stranger.
Other apps
Google Sheets has VLOOKUP with the same four arguments, so a formula copied out of Excel usually works there. Sheets has no XLOOKUP, which makes =INDEX(C2:C6, MATCH(A2, A2:A6, 0)) the form that survives a trip in both directions.
VBA reaches the same lookup through Application.WorksheetFunction.VLookup, which raises an error instead of returning #N/A when nothing matches. An error handler around it decides what the macro does with the missing case.
Practice
The exercise below uses a five-person directory and asks for the salary that belongs to E-104. Type the formula the way you would in Excel, with the comma-separated arguments in order, and press Check. The cell reference in the label tells you where the answer belongs. If it fails, the message names the part that went wrong rather than just saying "wrong".
Try it: exercise 1 of 5
In E2, look up the salary for the ID in D2 using the table in A1:C6.
Right employee, wrong column: the salary is column 3 of A:C. Column 2 holds the name.
Show the answer
Cell E2 holds 54000Try it: exercise 2 of 5
In B7, look up the lead time of the first Northwind supplier in A1:C6, using the partial name in A7 with a wildcard.
That is the country. Supplier is 1, country 2, lead time 3, so the third argument is 3.
Show the answer
Cell B7 holds 7Try it: exercise 3 of 5
In B6, look up the commission rate for the revenue in A6 across the bands in A2:B5, using approximate match.
Which band covers 4200? The one that starts at 1000, so the rate is column 2 of A2:B5. Column 1 would hand back 1000.
Show the answer
Cell B6 holds 0.05Open this exercise in the lesson (VLOOKUP: approximate match needs a sorted column)
Try it: exercise 4 of 5
In C6, return the units for the product in A6 and the quarter in B6, using VLOOKUP and letting MATCH find the column.
Both positions count from the Product column. MATCH returns 3 for Q2, and VLOOKUP counts 3 columns into A1:C5.
Show the answer
Cell C6 holds 110Open this exercise in the lesson (Two-way lookup with VLOOKUP and MATCH)
Try it: exercise 5 of 5
In B7, return the unit price for the SKU typed in B6 using the table A1:C5.
Count from the SKU column: product is 2, price is 3. Column 2 would return Clamp.
Show the answer
Cell B7 holds 3.8Do the whole exercise (VLOOKUP and XLOOKUP on the same table)
Questions
Why does VLOOKUP return #N/A when the value is clearly there?
Look at the characters, not the letters. A trailing space, a number stored as text, or a value pasted with a non-breaking space all produce a value that looks identical and compares as different. Select the cell, press F2, and read the formula bar, or test the two values with =EXACT(A2, D2).
Should I use VLOOKUP or XLOOKUP?
XLOOKUP is simpler and can look left. Use it in a new workbook if your version of Excel has it. VLOOKUP is still everywhere: old files, other people's templates, shared sheets. Knowing it is not optional, and knowing the exact-match form is what keeps it honest.
Is TRUE ever the right fourth argument?
Yes, for banded data such as tax brackets or shipping tiers, where the table is deliberately sorted and you want the nearest band below. In that case sort the column, lock the range, and say so in a note next to the formula. For anything keyed by an ID, name or code, use FALSE.