VLOOKUP in Excel: Step-by-Step for Beginners

VLOOKUP in Excel looks up a value in the first column of a table and brings back a matching value from another column in the same row. You use it to pull a price by product code, a salary by employee ID or an email address by name. This guide is for beginners: you will write your first VLOOKUP, understand each of its four parts, and learn how to fix the errors that trip most people up.

What does VLOOKUP do in Excel?#

VLOOKUP searches down the first column of a range until it finds your value, then moves across that row to the column you ask for. The "V" stands for vertical, because it searches down a column. (Its sister function HLOOKUP searches across a row instead.)

Think of it like finding a name in a phone book. You run your finger down the names, stop at the right one, then read across to the phone number.

VLOOKUP works in every version of Excel, including Excel 2010, 2016, 2019, 2021, Microsoft 365, Excel for Mac and Excel for the web. That is one reason it still appears in so many job tests.

VLOOKUP syntax: the four arguments#

VLOOKUP takes four arguments, and the first three are required.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The column number is the part beginners most often get wrong. It counts columns inside your table, not across the whole sheet. If your table starts in column C, then column C is 1, D is 2, and so on.

How to use VLOOKUP step by step#

The fastest way to learn VLOOKUP is to write one on real data. Here is a small product list:

You want the price of the code typed in F2 (P-104) to appear in G2.

Step 1: Click the result cell and start the formula#

Click G2 and type =VLOOKUP(. Excel shows the argument names in a tooltip as you type.

Step 2: Choose what to find#

Click F2, then type a comma. This is the lookup_value.

Step 3: Select the table#

Select A2:D6, then type a comma. The codes are in column A, the first column of this range, so VLOOKUP can search them.

Step 4: Count the column to return#

Price is the 4th column of A2:D6 (Code = 1, Product = 2, Category = 3, Price = 4). Type 4 and a comma.

Step 5: Ask for an exact match and press Enter#

Type FALSE, close the bracket and press Enter:

=VLOOKUP(F2,A2:D6,4,FALSE)

The result is 32, the price of the USB-C hub. Change F2 to P-102 and the result updates to 149.

Exact match vs approximate match#

Use FALSE (exact match) whenever you look up IDs, codes, emails or names. With FALSE, VLOOKUP either finds the exact value or returns #N/A, so you never get a silently wrong answer.

Use TRUE (approximate match) only for bands and tiers, such as tax brackets, commission rates or grade boundaries. Approximate match needs the first column sorted in ascending order, and it returns the row for the largest value that is less than or equal to your lookup value. Our guide to VLOOKUP approximate match for tiers and bands walks through that case.

If you leave the fourth argument out, Excel assumes TRUE. That default causes many wrong results, so always type FALSE unless you mean approximate.

How to copy a VLOOKUP down a column#

Lock the table with dollar signs before you copy the formula, or the range will shift as it moves down.

=VLOOKUP(F2,$A$2:$D$6,4,FALSE)

$A$2:$D$6 is an absolute reference: it stays the same in every row. F2 stays relative, so it becomes F3, F4 and so on. To add the dollar signs quickly, click inside the range in the formula bar and press F4 on Windows or ⌘T on a Mac. Then select the cells and fill down with Ctrl+D (Windows) or ⌘D (Mac).

Why does VLOOKUP return #N/A?#

VLOOKUP returns #N/A when it cannot find the lookup value in the first column of the table. Check these causes in order:

1. Typos or different spelling. P-104 and P104 are different values.
2. Extra spaces. "P-104 " with a trailing space will not match. Clean the data with =TRIM(A2).
3. Numbers stored as text. An ID of 1001 typed as text will not match the number 1001. Look for a small green triangle in the cell.
4. The value is not in the first column. VLOOKUP never searches columns to the right of the first one.
5. The table range is too short. If you added rows below the table, extend the range or use whole columns such as A:D.

To show a friendly message instead of #N/A, wrap the formula in IFERROR:

=IFERROR(VLOOKUP(F2,$A$2:$D$6,4,FALSE),"Not found")

Common VLOOKUP mistakes#

These mistakes come up again and again in practice tests:

• Counting the column from column A of the sheet instead of from the first column of the table.
• Forgetting FALSE, so the formula quietly uses approximate match on unsorted data.
• Not locking the table, so copied formulas search the wrong rows.
• Looking up a value in a column to the right of the return column. VLOOKUP cannot look left. Use INDEX and MATCH or XLOOKUP for that.
• A column number larger than the table. =VLOOKUP(F2,A2:D6,5,FALSE) returns #REF! because A2:D6 only has 4 columns.

A hidden risk: if someone inserts a new column inside your table, the column number does not update, so VLOOKUP starts returning the wrong column. XLOOKUP avoids this. See VLOOKUP vs XLOOKUP for when the newer function is the better choice.

VLOOKUP in job tests#

VLOOKUP is one of the functions employers test most often, along with SUMIF and pivot tables. A typical task gives you two tables, such as an order list and a price list, and asks you to bring the prices into the orders. Speed matters, so practise until you can write the formula without the tooltip. Our guide on how to pass an Excel test for a job interview covers the other skills these tests check.

For the full argument reference and more examples, see the VLOOKUP function guide.

Practise it#

Reading about VLOOKUP only gets you halfway. Try the live exercise on this page, then practise VLOOKUP exact match in a live spreadsheet with step-by-step feedback. When you're ready to go faster, the VLOOKUP practice lesson gives you job-style tasks to solve on your own.