VLOOKUP in Google Sheets searches the first column of a selected range and returns a value from another column in the same row. The safest starting formula is =VLOOKUP(E2,A2:C100,3,FALSE): it looks for the value in E2 in column A and returns the matching value from the third column of the range, column C. The FALSE argument requests an exact match.
VLOOKUP syntax in Google Sheets
Google Sheets uses the following syntax:
=VLOOKUP(search_key, range, index, [is_sorted])| Argument | Meaning | Example |
|---|---|---|
search_key | The item you want to find | E2 or "SKU-104" |
range | Table to search; its first column must contain the lookup values | A2:C100 |
index | Column position to return, counted from the left edge of the range | 3 |
is_sorted | FALSE for exact match; TRUE for approximate match | FALSE |
For most product IDs, employee IDs, email addresses and order numbers, use FALSE. Omitting the fourth argument can lead to approximate-match behavior, which may silently return an unexpected row if the lookup column is not sorted as required.
Example 1: find a product price by SKU
Imagine a sheet with this data:
| A: SKU | B: Product | C: Price |
|---|---|---|
| SKU-101 | Notebook | 12 |
| SKU-102 | Pen set | 8 |
| SKU-103 | Desk lamp | 29 |
Enter SKU-103 in E2 and place this formula in F2:
=VLOOKUP(E2,A2:C4,3,FALSE)The result is 29. Here’s why: VLOOKUP looks down A2:A4, finds SKU-103, and returns the third column of A2:C4 from that same row. The index is 3 even though the result lives in sheet column C; the index is relative to the selected range.
Make the range stay fixed when copying
If you want to look up many SKUs from E2 downward, lock the table range:
=VLOOKUP(E2,$A$2:$C$100,3,FALSE)Drag the formula down. E2 becomes E3, E4 and so on, but the dollar signs keep the lookup table from sliding to a different location. This prevents one of the most common spreadsheet errors in copied formulas.
Example 2: look up a name and return a department
Suppose A contains employee IDs, B contains names and C contains departments. With an employee ID in F2, use:
=VLOOKUP(F2,A2:C200,3,FALSE)Do not put a person’s name in F2 unless names are in the first column of the lookup range. If you need to search column B instead, change the range to B2:C200 and use index 2:
=VLOOKUP(F2,B2:C200,2,FALSE)This is a useful way to avoid rearranging the original table. However, VLOOKUP still cannot return a column to the left of the lookup column. For that, consider XLOOKUP or INDEX/MATCH.
Example 3: VLOOKUP across two sheets
For a lookup table on a tab named Products, use:
=VLOOKUP(A2,Products!$A$2:$D$500,4,FALSE)The formula looks up the current sheet’s A2 value in the first column of the Products range and returns its fourth column. If the sheet name contains spaces, wrap it in single quotation marks:
=VLOOKUP(A2,'Product Catalog'!$A$2:$D$500,4,FALSE)For data stored in a completely separate spreadsheet file, you may need IMPORTRANGE and access authorization. Cross-file access adds complexity and potential delays, so test the import separately before embedding it in a lookup.
Exact match vs approximate match
| Mode | Formula | Best for |
|---|---|---|
| Exact | =VLOOKUP(E2,A2:C100,3,FALSE) | SKUs, usernames, IDs, codes |
| Approximate | =VLOOKUP(E2,A2:C100,3,TRUE) | Ordered score bands and threshold tables |
Approximate matching requires the first column of the range to be sorted in ascending order. For example, a grade table with thresholds 0, 60, 70, 80 and 90 can map a numeric score to a letter grade. If you use approximate matching on unsorted thresholds, results can be incorrect.
How to handle #N/A when a value is not found
An exact VLOOKUP returns #N/A when the search key is not present. For a reader-friendly result, wrap the lookup in IFNA:
=IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found")IFNA specifically handles the not-found error. IFERROR catches more types of errors, including mistakes in your formula or range, so it can conceal problems that deserve investigation. Prefer IFNA when the only expected issue is a missing lookup key.
Fix VLOOKUP not working: six common mistakes
1. The search column is not first
VLOOKUP only searches the leftmost column of the range. If the search key is in B, do not choose a range beginning in A unless A also contains that key. Change the range or use a different lookup function.
2. Wrong column index
If the range is C2:F50, its first column is index 1 (C), second is index 2 (D), third is index 3 (E), and fourth is index 4 (F). Index 5 is outside this four-column range and causes an error.
3. Numbers stored as text
A numeric ID such as 1042 may not match a text string containing the same characters. Inspect the source cells, their formats and how the data was imported. Converting both sides to the same data type is safer than applying random formatting changes.
4. Extra spaces and hidden characters
A copied value may contain leading spaces, trailing spaces or nonbreaking spaces. TRIM removes ordinary extra whitespace, but some hidden characters need other cleanup methods. Use a helper column if necessary so the lookup key and source data are normalized consistently.
5. The table range moves when you copy the formula
Use absolute references such as $A$2:$D$200 for a fixed lookup table. Otherwise the selected range shifts with every copied formula and may exclude the intended match.
6. Approximate match was enabled accidentally
Always specify FALSE when looking up a specific ID or exact text. The fourth argument is easy to overlook, but it is critical to reliable results.
VLOOKUP vs XLOOKUP vs INDEX/MATCH
| Function | Strength | Consideration |
|---|---|---|
| VLOOKUP | Familiar, simple left-to-right lookup | Search column must be first in the range |
| XLOOKUP | Separate search and return ranges; can look left | Check availability and syntax in your spreadsheet environment |
| INDEX + MATCH | Flexible return column selection | Longer formula for beginners |
For example, to find a product ID in B2:B100 and return a value from A2:A100, an INDEX/MATCH approach is:
=INDEX(A2:A100,MATCH(E2,B2:B100,0))Here MATCH finds the row position of an exact key and INDEX returns the value from the corresponding row in column A. Unlike VLOOKUP, it can return a value located to the left of the search column.
Performance and maintenance tips
- Prefer a reasonably bounded range such as
A2:D5000over huge full-column ranges when working with many repeated lookups. - Use stable unique IDs where possible; duplicate keys make a first-match lookup ambiguous.
- Put a clear header above every source column and document which column is used as the key.
- Check a few known results manually after importing or reorganizing data.
- If your Google Sheets locale uses semicolons instead of commas as formula argument separators, adapt the examples to your locale.
Frequently asked questions
Does VLOOKUP return the first or last match?
It returns the first matching row it finds in the search column. If duplicate keys are present, the formula does not automatically pick the newest record.
Can VLOOKUP search to the left?
No. VLOOKUP returns values from columns at or to the right of the lookup column within its selected range. Use XLOOKUP or INDEX/MATCH for leftward lookups.
Why does VLOOKUP show #N/A when the value is visible?
Check whether the values have the same data type, whether spaces or invisible characters differ, and whether the lookup key is in the first column of the selected range.
What does FALSE mean in VLOOKUP?
It requests an exact match. For IDs and text labels, this is usually the intended setting.
How do I VLOOKUP from another tab?
Prefix the range with the sheet name and an exclamation mark, such as Products!A2:D100. Put names containing spaces in single quotes.
Sources and next steps
For current syntax and behavior, refer to Google’s official VLOOKUP function documentation. For additional spreadsheet basics, see our Google Sheets download and offline guide. Start with a three-column practice table, test one exact match, then lock the range before copying the formula.
Other Articles
