How to Use VLOOKUP in Excel (Step by Step, With Examples)

You’re staring at a product code and you need its price. The price lives in another table — maybe another sheet. Typing it by hand works for five rows. It does not work for five hundred. That’s the job VLOOKUP was built for: look up a value in the first column of a table and return the matching value from another column.

What VLOOKUP does

VLOOKUP takes something you know (a product code, an employee ID, a customer name) and finds the matching row in a table, then returns a value from that row. Think of it as a phone book: you give it a name, it gives you the number.

The syntax

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

  • lookup_value — the value to find (for example, the product code in cell A2).
  • table_array — the table to search. The lookup column must be the first column of this range.
  • col_index_num — which column of the table to return, counting from the left starting at 1.
  • range_lookup — FALSE for an exact match, TRUE for an approximate match. In practice, use FALSE almost every time.

Step-by-step example

Suppose you have a price list in columns A:B (product code, price) and an order list where you need to fill in prices.

  1. Click the first cell where the price should go.
  2. Type =VLOOKUP(A2,$A$10:$B$14,2,FALSE) — A2 is the code to look up, $A$10:$B$14 is the price table, 2 means “return the second column”, FALSE means “exact match only”.
  3. Press Enter. The price appears.
  4. Drag the fill handle down to copy the formula to the other rows.

Use dollar signs ($A$10:$B$14) around the table so the range doesn’t shift as you copy the formula down. This is the single most common beginner mistake — without the dollar signs, row 3 looks up a table that has slid down by one row, and the results go wrong silently.

Worked example

Product code Product Price
A1001 Notebook $4.50
A1002 Pen set $6.20
A1003 Stapler $8.90
A1004 Binder $3.75

With the code A1003 in cell A2, =VLOOKUP(A2,$A$2:$C$5,3,FALSE) returns $8.90. The 3 counts columns from the left of the table: code is 1, product is 2, price is 3.

Common errors

#N/A — value not found. The lookup value isn’t in the table. Check for extra spaces (A1003 with a trailing space is not A1003), or numbers stored as text. TRIM() removes extra spaces; multiplying a text number by 1 converts it to a real number.

#REF! — column number too big. You asked for column 5 from a 3-column table. Count the columns again, starting at 1.

Wrong answer, no error. You left out the last argument or used TRUE. With TRUE, VLOOKUP returns the closest value below your lookup value and needs the table sorted — almost never what you want. Always write FALSE unless you know why you need an approximate match.

Lookup column isn’t first. VLOOKUP can only search the first column of the table. If the code is in column B and the price in column A, VLOOKUP cannot do it — use XLOOKUP or INDEX-MATCH instead.

Practice file

Download the VLOOKUP practice file (.xlsx) — it contains the price list above, an order sheet with the formulas already filled in, and a blank sheet for you to try it yourself.

Google Sheets

VLOOKUP works exactly the same way in Google Sheets, including the FALSE argument. The same file opens in Sheets and the formulas keep working.

All amounts in this post are in nominal dollars — they are not adjusted for inflation. Example prices are illustrative, not market data.