How to Use VLOOKUP in Google Sheets

You have a price list on one part of the sheet and a list of orders that only shows the product name. VLOOKUP finds each name in the price list and returns the price next to it.

Put the search column first in the lookup table

VLOOKUP only searches the first column of the range you give it, and returns a value from a column to the right of it. Keep the product names in the leftmost column of the table.

For this example, A2:A4 holds Notebook, Stapler, and Desk lamp, and B2:B4 holds 4.50, 12.00, and 38.00, with headers in row 1.

Type the formula

=VLOOKUP("Stapler", A2:B4, 2, FALSE)

• search_key is the value to find, here the text "Stapler".
• range is the block to search. VLOOKUP looks for the search key in the first column of this range only.
• index is the column of the range to return, counted from 1. Price is the second column, so 2.
• is_sorted set to FALSE forces an exact match. Leave it as FALSE unless the first column is sorted and an approximate match is acceptable.

The cell shows 12.

Point the search key at a cell and lock the range

For a list of orders where the product names sit in D2:D20, enter this in E2:

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

The $ signs lock the range so it stays on A2:B4 when the formula is copied. Without them the range shifts down one row per copy and the lookups start missing.

Copy the formula down the column

Select E2 and drag the small square at its bottom-right corner down to E20, or double-click it to fill to the end of the neighboring data. Each row now shows the price for the product named in column D.

If the formula returns #N/A. There is no exact match for that search key. Check for spelling differences and trailing spaces in either list. To show something friendlier than the error, wrap the formula: =IFERROR(VLOOKUP(D2, $A$2:$B$4, 2, FALSE), "Not found").

XLOOKUP is the newer alternative. =XLOOKUP(D2, A2:A4, B2:B4) takes the search key, the column to search, and the column to return, in that order. The search column can be anywhere in the table, and there is no column index to count.