VLOOKUP in Google Sheets: How to Use It

To use VLOOKUP in Google Sheets, enter =VLOOKUP(search_key, range, index, FALSE) in the cell where you want the answer, replacing the arguments with your lookup value, table range, and return-column number. This finds an exact match in the first column of a range and returns a value from a column to its right. Before you start, arrange your data so the values you want to search are in the range’s leftmost column.

What You’ll Need

A Google Sheets spreadsheet open in a web browser, with both the value you want to find and the lookup table available.
A lookup table with the search values in its first column and the result you want in a column to the right.
An empty cell for the result, plus a clear idea of whether the search should find an exact match. For most lists, choose an exact match.

Step-by-Step

Set up a simple lookup table. For this example, create a sheet named Products. Put a product ID in column A, the product name in column B, and the unit price in column C. Use row 1 for headings and rows 2 through 5 for data: P-104 / Trail Mug / 12.50; P-205 / Desk Lamp / 28.00; P-318 / Cable Organizer / 8.75; and P-422 / Notebook Set / 6.40. Keep each product’s details on the same row. The values in column A are the keys that VLOOKUP will search.
Choose the cell that contains the search key. On another sheet, or in an unused area of the same sheet, enter a product ID in a cell. For example, type P-318 into E2. The key must match the value in the product table; an ID with an extra space or a different character may not match. You can type a key directly or have it supplied by another cell or form.
Select the cell where you want the result. Click an empty cell, such as F2, beside the key. That is where the product name will appear. A VLOOKUP formula can go in any suitable empty cell; it does not have to be next to the lookup key, but using adjacent cells makes a list easier to fill down and review.
Enter the VLOOKUP arguments in order. The first argument is the search key, the second is the range containing the lookup table, the third is the number of the column to return, and the fourth specifies whether to use an approximate or exact match. The column number is counted from the left edge of the selected range, not from the worksheet’s column letters. In this example, the product name is the second column in the range.
Use an exact-match formula for the product name. Assuming E2 contains P-318 and the Products sheet has IDs in A2:A5, names in B2:B5, and prices in C2:C5, enter this formula in F2. The search range starts at A2, the first column of the table, and the number 2 asks for the value in its second column. FALSE tells Sheets to look for an exact match.
With P-318 in E2 and the sample product table in Products!A2:C5, enter this formula in F2:
=VLOOKUP(E2,Products!A2:C5,2,FALSE)
Press Enter. The result is Cable Organizer, because P-318 is on row 4 of the Products sheet and its name is in column B.
Change the column number to return another field. The lookup range can include several result columns. If you want the unit price instead of the product name, use 3 because price is the third column in the A:C range. The lookup key and range remain the same; only the return-column number changes.
Assuming E2 contains P-318 and Products!A2:C5 contains the product IDs, names, and prices in that order, enter this formula in G2:
=VLOOKUP(E2,Products!A2:C5,3,FALSE)
The returned result is 8.75, the unit price for the Cable Organizer. Format G2 as currency if you want it displayed with a currency symbol.
Make the table range stay fixed when copying the formula. If you plan to use the formula for many lookup keys, put the first key in E2, enter the formula in F2, and then copy it down. Lock the table range with dollar signs so it does not shift as the formula moves. The reference to E2 should remain relative so it changes to E3, E4, and so on.
Assuming keys are in E2:E100 and the lookup table is still in Products!A2:C100, enter this formula in F2 and copy it down:
=VLOOKUP(E2,Products!$A$2:$C$100,2,FALSE)
For a row with P-318 in column E, the result will be Cable Organizer. To fill down, select F2 and drag the small square at the bottom-right corner of the cell down the column, or copy F2 and paste into the cells below.
Check the result and the formula. Confirm that the returned value belongs to the key on the same row. Click the result cell to review the formula in the formula bar. Check that the range begins with the key column, that the index points to the intended field, and that the final argument is FALSE for an exact match. If the formula is correct, repeat it for other keys or adjust the range to include more product rows.

In general, VLOOKUP is useful when a row has a unique identifier, such as a product ID, employee number, or order code, and you want to retrieve one related field. It returns one value per formula. To return several different fields, use separate VLOOKUP formulas with different column numbers, or consider a different lookup approach if your sheet needs a more flexible result.

Common Mistakes

Putting the lookup column somewhere other than the range’s first column: VLOOKUP searches only the first column of the range. If the IDs are in column C but the selected range begins at column A, it searches A instead of C. Select a range that starts with the ID column, or reorganize the table so the lookup column comes first.

Using the worksheet column letter as the index: The index is counted within the chosen range. If your range is B:D, column B is index 1, C is index 2, and D is index 3. It is not the same as the sheet’s column number.

Leaving out the exact-match setting: If the fourth argument is omitted, VLOOKUP uses an approximate match by default. That can return a misleading result when keys are unsorted or when the search is for a specific name, ID, or code. Add FALSE explicitly for exact matching.

Including a heading in the lookup range: A header row can be harmless in many cases, but it is unnecessary and can create confusing results if someone searches for the heading text. Start the range at the first row of actual records, or use a consistently defined range that excludes headings.

Expecting the formula to search left: The returned column must be to the right of the first column in the selected range. VLOOKUP cannot return a value from a column to the left of its search column. Adjust the layout or choose another lookup method when the value you need is on the left.

Tips

Keep the lookup table tidy: use one header row, avoid merged cells, and keep each record on a single row. Make sure each key is unique if you expect one specific result. If there are duplicate keys, VLOOKUP returns the first matching row it encounters, so the returned value may not be the one you intended.

Use a fixed range that is large enough for the records you expect to add, or update the range when the table grows. A range that stops at row 100 will not include a new record placed on row 101. You can also use a whole-column range, but a data-only range makes the intended table boundaries easier to see and avoids including the heading.

Check whether identifiers are stored consistently. A numeric code stored as a number may not behave like the same-looking code stored as text, and leading zeros can matter for IDs. For identifiers such as 0017, store the key as text in both places so the zeros are preserved. Avoid treating product codes or postcodes as quantities.

For formulas copied down a list, lock the table range with dollar signs while leaving the lookup-key cell relative. If the sheet is shared, add a short note explaining the table layout and why the formula uses an exact match. This can help another editor avoid changing FALSE to TRUE or shifting the range accidentally.

Google Sheets uses the same basic VLOOKUP argument order as Excel, but the instructions here use Google Sheets in a browser. If a formula using commas produces a parse error, your spreadsheet’s locale may expect semicolons between arguments. Check the spreadsheet locale under File > Settings and use the separator accepted by that sheet.

Troubleshooting

The cell shows #N/A: With an exact match, this usually means the key is not present in the first column of the selected range. Check for misspellings, leading or trailing spaces, different punctuation, or a number-versus-text mismatch. Verify that the range includes the row containing the key. You can also use Data > Data cleanup > Trim whitespace to remove extra spaces from selected data, then try again.

The answer is from the wrong row: Confirm that the last argument is FALSE. If you omit it or use TRUE, Sheets can perform an approximate match, which is intended for appropriately sorted data and can be unsuitable for IDs or names. Also check whether duplicate keys exist; VLOOKUP returns the first match, not every matching row.

The formula shows #REF! or #VALUE!: Check that the column index is a positive number and does not exceed the number of columns in the selected range. For example, a range with three columns cannot return index 4. Make sure the range reference is valid and that the formula has all required arguments.

Sheets displays a formula parse error: Check for a missing comma, unmatched parenthesis, or misspelled function name. If the formula uses commas but your locale expects semicolons, replace the argument separators with the locale’s separator. Do not put quotation marks around cell references such as E2; the formula should refer to the cell itself.

The result is blank or looks unexpected: Inspect the source cell in the return column. VLOOKUP may be correctly returning an empty cell, or the key may match a record whose result field has not been filled in. Also check the return-column index and confirm the selected range starts with the intended lookup column.

Frequently Asked Questions

What is the simplest VLOOKUP formula in Google Sheets?
For an exact match, use a search-key cell, a range whose first column contains that key, the number of the return column within that range, and FALSE as the final argument. The worked example above finds a product ID and returns its name.

Does VLOOKUP in Google Sheets require the data to be sorted?
No, not when you use FALSE for an exact match. Sorting may make a table easier to browse, but it is not required for that lookup. Approximate matching is different and relies on sorted data, so use it only when you specifically need that behavior.

Can VLOOKUP return more than one column?
A standard VLOOKUP formula returns one column’s value for each search key. You can use additional VLOOKUP formulas with different index numbers to retrieve other fields. If you need a more flexible or multi-column result, choose a lookup method suited to that layout.

Can VLOOKUP search from right to left?
No. It searches the first column of its range and returns a value from a column to the right. Make the lookup column the range’s leftmost column, rearrange the data, or use another lookup function for a right-to-left search.

Why does copying my formula change the range?
Cell references normally adjust when you copy a formula. Put dollar signs around the lookup table range to keep it fixed, while leaving the search-key reference relative so it changes for each row.

Is VLOOKUP different in Google Sheets and Excel?
The basic function and argument order are very similar. This guide uses Google Sheets references and browser menus; when moving a spreadsheet between apps, check that sheet names, range references, and the spreadsheet locale’s argument separators are appropriate.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.