VLOOKUP in Google Sheets searches the first column of a range for a value and returns a value from another column in the same row. The formula is =VLOOKUP(search_key, range, index, is_sorted). For example, =VLOOKUP(“P-102”, A2:C100, 3, FALSE) finds the product code P-102 in column A and returns the matching value from the third column, column C. Use FALSE at the end in almost every case so it looks for an exact match.
Below is what each part of the formula means, worked examples, how to look up data on another sheet, and how to fix the errors people run into most.
VLOOKUP syntax explained
| Part | What it means | Example |
|---|---|---|
| search_key | The value to look for, or a cell that contains it | “P-102” or E2 |
| range | The table to search. The search_key must be in its first column. | A2:C100 |
| index | Which column of the range to return, counting from 1 | 3 |
| is_sorted | FALSE for an exact match. TRUE (the default) for the closest match in sorted data. | FALSE |
How to use VLOOKUP: a worked example
Say you have a product list with codes in column A, product names in column B and prices in column C, and you want to type a code in cell E2 and see its price in F2.
- Click cell F2.
- Type =VLOOKUP(E2, $A$2:$C$100, 3, FALSE).
- Press Enter. The price for the code in E2 appears.
- To look up a list of codes, type them in E2, E3, E4 and so on, then drag the fill handle at the bottom-right corner of F2 down the column.
The dollar signs lock the range so it doesn’t shift when you copy the formula down. Change the 3 to 2 to return the product name instead of the price.
VLOOKUP from another sheet
To search a table on a different tab in the same file, put the sheet name in front of the range, followed by an exclamation point:
=VLOOKUP(A2, ‘Price List’!A:C, 3, FALSE)
Use single quotes around the sheet name if it contains spaces. You can also start typing the formula, then click the other sheet’s tab and select the range, and Sheets fills in the reference for you.
VLOOKUP from another Google Sheets file
Combine VLOOKUP with IMPORTRANGE to search a different spreadsheet:
=VLOOKUP(A2, IMPORTRANGE(“spreadsheet_url”, “Sheet1!A:C”), 3, FALSE)
The first time, Sheets shows a #REF! error with an Allow access button. Click it to connect the two files.
Exact match vs approximate match
- FALSE (exact match): Returns a result only when it finds exactly the search key. Use this for codes, names, IDs and email addresses.
- TRUE (approximate match): Returns the closest value that’s less than or equal to the search key. The first column must be sorted in ascending order. This is useful for bands, such as finding a letter grade from a score table (0 = F, 60 = D, 70 = C and so on) or a tax rate from income brackets.
If you leave the last argument out, Sheets uses TRUE, which is a common cause of wrong results.
Fix common VLOOKUP errors
| Error | Cause | Fix |
|---|---|---|
| #N/A | The search key wasn’t found | Check spelling, extra spaces and number vs text formatting. Wrap with IFERROR to show a blank or message. |
| #REF! | The index is larger than the number of columns in the range | Widen the range or lower the index |
| #VALUE! | The index is 0 or not a number | Use a column number of 1 or more |
| Wrong result | is_sorted left as TRUE on unsorted data | Add FALSE as the last argument |
| Results shift when copied | Range not locked | Add $ signs, such as $A$2:$C$100 |
To hide #N/A errors, use =IFERROR(VLOOKUP(E2, $A$2:$C$100, 3, FALSE), “Not found”).
Extra spaces are a sneaky cause of #N/A. Use =VLOOKUP(TRIM(E2), …), or clean the data with Data > Data cleanup > Trim whitespace.
VLOOKUP limitations and alternatives
- It can only look to the right. The search column must be the first column of the range.
- Inserting columns can break the index. If you add a column in the middle of the range, the column number no longer points where you meant.
- It returns only the first match. Duplicate keys further down are ignored.
Google Sheets also has XLOOKUP, which fixes the first two problems: =XLOOKUP(E2, A2:A100, C2:C100, “Not found”) searches column A and returns column C, in any order, with a built-in message when nothing matches. INDEX and MATCH together do the same and work in older spreadsheets. To return every match instead of the first, use FILTER.
Tips
- VLOOKUP isn’t case-sensitive, so “apple” matches “Apple”.
- Use whole columns such as A:C as the range if your table keeps growing.
- Numbers stored as text won’t match real numbers. Use Format > Number to make both columns the same type.
Related: specify a header row in Google Sheets.
Related: write code in Google Sheets with Apps Script.
Frequently asked questions
Can VLOOKUP return more than one column?
Yes. Use an array of column numbers, such as =ARRAYFORMULA(VLOOKUP(E2, A2:D100, {2,3,4}, FALSE)), to return columns 2, 3 and 4 side by side.
Does VLOOKUP work in the Google Sheets mobile app?
Yes. Type the formula in a cell the same way. It’s easier to build long formulas on a computer, but they work everywhere.
Is VLOOKUP the same in Google Sheets and Excel?
The syntax is the same. Google Sheets calls the last argument is_sorted and Excel calls it range_lookup, but both use FALSE for exact match.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.