Google Sheets VLOOKUP Function Tutorial
This guide explains how to use VLOOKUP in Google Sheets for data lookups, and how SurveyMars Vlookup survey linking supports response data lookup without spreadsheet formulas.
1. Understanding VLOOKUP
When you already know part of the information in a spreadsheet, VLOOKUP searches by row for related values. For example, to buy an orange, you can look up its price in a price table by searching for "Orange."
VLOOKUP (vertical lookup) searches the first column of a range for a key, then returns the value from the Nth column to the right in that row. Use it to match IDs, order numbers, phone numbers, or employee IDs to names, prices, status, and other fields.
Tip: The lookup key column must be the leftmost column in range. VLOOKUP only reads columns to the right; you cannot look up a column on the left using a column on the right.
Syntax
=VLOOKUP(search_key, range, index, [is_sorted])
search_key: The value to find—a cell reference or literal text/number. It must appear in the first column of range.
range: The data area that includes the key column and return columns. You can use a named range; for another sheet, use Sheet2!$A$2:$B$100.
index: The column number to return, counting from the leftmost column of range, starting at 1.
is_sorted (optional): FALSE for an exact match (recommended); TRUE for approximate match (sort the first column ascending first). If omitted, the default is TRUE.
2. VLOOKUP Examples
Example 1: Enter a fruit name in G9, then use these formulas to look up price in B4:D8 (price is in column 3):
=VLOOKUP(G9, B4:D8, 3, FALSE)
=VLOOKUP("Apple", B4:D8, 3, TRUE)
The examples below are based on Google’s official help. Click a title to expand or collapse; click Try it to practice in a copy of the sample spreadsheet.
Basic VLOOKUP examples
Look up different search keys with VLOOKUP
Use VLOOKUP to find prices for "Orange" and "Apple."
Note: You can use different search keys (such as "Apple" or "Orange"). To return a value instead of an error, each key must exist in the first column of range. You can also use a cell reference (for example G9) instead of typing the key.
| Description | Formula and result |
|---|---|
search_key is "Orange" | =VLOOKUP("Orange", B4:D8, 3, FALSE)Result = $1.01 |
search_key is "Apple" | =VLOOKUP("Apple", B4:D8, 3, FALSE)Result = $1.50 |
search_key is a cell reference to "Apple" (G9) | =VLOOKUP(G9, B4:D8, 3, FALSE)Result = $1.50 |
Look up different column indexes with VLOOKUP
Look up the quantity of "Orange" in the second column (quantity).
Note: Columns in range are numbered left to right, starting at 1. Specify the column number for the value you want—for example, column 2 is quantity.
| Description | Formula and result |
|---|---|
index is 2Find the quantity of oranges—the second column in range. | =VLOOKUP(G3, B4:D8, 2, FALSE)Result = 5 |
Exact match vs. approximate match in VLOOKUP
- Exact match (FALSE): Find an exact ID.
- Approximate match (TRUE): Find the closest ID (first column must be sorted ascending).
Note: If you search for ID 102 but it is not in the table, approximate match may return 101 (the largest value less than 102). If the first column is not sorted ascending, results can be wrong. Use exact match for everyday work.
Important: Before using approximate match, sort the search key column in ascending order.
| Match type | Formula and result |
|---|---|
| Exact match | =VLOOKUP(G6, A4:D8, 2, FALSE)Result = "Apple" |
| Approximate match | =VLOOKUP(G3, A4:D8, 2, TRUE) or omit the fourth argumentResult = "Banana" |
Common VLOOKUP scenarios
Replace VLOOKUP error values
When the search key is missing, wrap VLOOKUP in IFNA() to replace #N/A with custom text.
| Description | Formula and result |
|---|---|
At first, VLOOKUP returns #N/A because "Pencil" is not in the Fruit column.IFNA() replaces #N/A with the second argument—in this example, NOT FOUND. | =IFNA(VLOOKUP(G3, B4:D8, 3, FALSE), "NOT FOUND")Result = NOT FOUND |
Tip: Use IFERROR() to handle other errors such as #REF!.
Use multiple criteria with VLOOKUP
VLOOKUP cannot match on multiple criteria directly. Add a helper column and combine columns with & to build a single key.
| Step | Formula and result |
|---|---|
1. Combine "First Name" and "Last Name" with & to create a Helper column. | =C4&D4, then fill down from B4 to B8 to build the Helper column. |
| 2. Use cell B7 (JohnLee) as the search key. | =VLOOKUP(B7, B4:E8, 4, FALSE)Result = Support |
Use wildcards or partial matches in VLOOKUP
? matches one character; * matches any sequence. Use with FALSE (exact match).
| Description | Formula and result |
|---|---|
"St*" matches names that start with St, such as "Steve," "St1," "Stock," or "Steeeeeeve." | =VLOOKUP("St*", B4:D8, 3, FALSE)Result = Marketing |
3. Troubleshooting
Match your situation below. Most issues come down to four things: key column position, format, exact vs. approximate match, and whether the range is locked.
1. The data is in the table—why do I still get #N/A?
A: Check in order—① Is the lookup value in the leftmost column of range? ② Do text and number formats match (for example 001 vs. 1)? ③ Are there extra spaces at the start or end? ④ Does the source data actually contain the value? Use TRIM to remove spaces, and VALUE or TEXT to align formats before you look up again.
2. The formula suddenly shows #REF!—did I use the wrong column number?
A: Usually index is larger than the number of columns in range. Count again from the leftmost column of range and fix the column number.
3. I see #VALUE!—what is wrong with the formula?
A: Common causes: index is less than 1; the fourth argument is not TRUE or FALSE; range is invalid. Use index ≥ 1 and set the fourth argument to FALSE (exact match).
4. I get a result, but it does not match the sheet?
A: Check whether you used TRUE (approximate match)—for daily use, switch to FALSE. If the first column has duplicate keys, VLOOKUP returns only the first match. If many rows show the same wrong value, range may have shifted when you copied the formula.
5. Only the first row is correct after I copy the formula down?
A: Lock the lookup range with absolute references, such as $B$4:$D$100, or across sheets Sheet2!$A$2:$B$500, so the range does not move when you fill down.
6. I need to look up employee ID from name—why does VLOOKUP fail?
A: VLOOKUP only searches left to right; the key column must be the leftmost column in range. Move the ID column to the left, or use INDEX + MATCH or XLOOKUP. If the formula errors, check whether your locale uses commas or semicolons as separators (some regions use ;).
4. SurveyMars: Response data lookup with Vlookup survey linking
To pull fields from past responses or profiles while someone is taking your survey, you do not need to export to a sheet and write VLOOKUP. SurveyMars Vlookup survey linking handles response data lookup in the platform: after the respondent enters a match key, the system searches the linked survey and auto-fills the questions you configured.
How this maps to VLOOKUP
| VLOOKUP concept | SurveyMars setting |
|---|---|
range (lookup range) | Linked survey data |
search_key (lookup value) | Match fields the respondent enters |
index (return column) | Questions to auto-fill |
Use cases
- Member or employee profiles already collected; a new survey pulls basics from a few match keys
- Event registration verification and completing missing information
- Multi-wave surveys aligned with fields from earlier responses
Configuration
- Visual setup: Select the linked survey and fill fields in the editor—no formulas
- Lookup on response: Match and fill before submit, with less export-and-merge work
- Access control: Data stays in SurveyMars so you can manage who sees what
Want to set up the linked survey, match fields, and auto-fill questions?
FAQ
Q: My phone number or employee ID looks identical—why won't it match?
A: Often the values look the same but are not—one column is text and another is a number, or there are spaces or leading zeros. Compare with =TRIM(A1) to see if cells are truly equal; use VALUE or TEXT to align formats. If you still get #N/A, work through section 3 above.
Q: I don't want a sheet full of #N/A when there is no match—is there a friendlier option?
A: Wrap VLOOKUP in IFNA and show custom text when nothing is found, for example =IFNA(VLOOKUP(G3, B4:D8, 3, FALSE), "Not found"). See Replace VLOOKUP error values in section 2 for a full example.
Q: My price table is on another sheet—how do I write the formula?
A: Put the sheet name before range, for example =VLOOKUP(G9, PriceList!$A$2:$D$200, 3, FALSE). If the sheet name has spaces, use quotes: '2024 Prices'!$A$2:$D$200. Lock the range with $ so copying the formula does not shift it.
Q: I have two lists to align—should I use VLOOKUP or INDEX+MATCH?
A: If the key is in the leftmost column of the range and you only need columns to the right, VLOOKUP is enough. To look up from a column on the right (such as name) and return a column on the left (such as employee ID), use INDEX + MATCH or XLOOKUP. Google Sheets supports XLOOKUP with straightforward syntax.
Q: Can respondents enter a phone number and get a name auto-filled—without exporting to Excel for VLOOKUP?
A: Yes. Add a Vlookup survey linking question in SurveyMars, configure the linked survey and fill fields, and lookup runs during the response—no export or formulas required. See section 4, or click the green button above for the official help article.
