How to Use VLOOKUP in Excel

You have a list of products and prices on one sheet, and you need to pull the price for a given product into another cell. VLOOKUP searches the first column of a range for a value and returns something from the same row.

Set up the lookup table

Put the value you will search for in the first column of the table. VLOOKUP only looks in the leftmost column of the range you give it, so a price list must have the product names on the left and the prices to the right. For this example, the table sits in A2:B5:

Product    Price
Notebook   4.50
Pen        1.20
Stapler    8.99
Tape       2.75

Type the formula

Click the cell where the result should appear and enter:

=VLOOKUP("Pen", A2:B5, 2, FALSE)

The arguments, in order:

• lookup_value: the value to find, here the text "Pen".
• table_array: the range to search, A2:B5. The lookup column must be the first column of this range.
• col_index_num: which column of the range to return, counted from the left. 2 returns the Price column.
• range_lookup: FALSE for an exact match. Leaving it out or using TRUE gives an approximate match, which only works on a sorted list.

Press Enter. The cell shows 1.20.

Reference a cell instead of typing the value

Replace the hard-coded text with a cell reference so the formula updates when the input changes:

=VLOOKUP(D2, A2:B5, 2, FALSE)

Type Stapler in D2 and the result becomes 8.99.

Lock the table range before copying down

If you fill the formula down a column, the table range shifts with each row and stops finding matches. Lock it with dollar signs:

=VLOOKUP(D2, $A$2:$B$5, 2, FALSE)

Press F4 (Mac: Cmd + T) while the cursor is inside the range in the formula bar to add the dollar signs. Alternatively, select the data, click Insert, Table, and use the table name in the formula, such as Table1, so the range grows as rows are added.

If the formula returns #N/A. No exact match was found. Check for extra spaces or for a number stored as text on one side, then wrap the formula so the sheet shows something readable instead: =IFERROR(VLOOKUP(D2, $A$2:$B$5, 2, FALSE), "Not found").

If you have Microsoft 365. XLOOKUP replaces VLOOKUP and does not need the lookup column to be first: =XLOOKUP(D2, A2:A5, B2:B5, "Not found"). The last argument is the text to show when nothing matches.