VLOOKUP Google Sheets Tutorial: Formulas, Examples & Fixes

In Google Sheets, VLOOKUP searches the first column of a table and returns a value from a column to its right. For example, if E2 contains a product ID and A2:C100 holds IDs, names, and prices, use =VLOOKUP(E2,A2:C100,3,FALSE) to return that ID’s price as an exact match.

What VLOOKUP Does

VLOOKUP is short for “vertical lookup.” It is useful when a table is organized in rows, with the item you want to search for in its leftmost column. The function finds that item in the first column of a selected range, then returns a value from the same row in another column.

For instance, a product list might have a product code in column A, a name in B, and a price in C. You can enter a code elsewhere and have a formula retrieve its price. This is handy for filling in details on order forms, matching employee IDs to departments, or finding a customer’s contact information from a customer number.

VLOOKUP does not search every column in the selected range. It searches only the range’s first column, and it cannot return a value from a column to the left of that column. Those two details explain many lookup mistakes and help determine when another function is a better choice.

Set Up a Basic VLOOKUP in Google Sheets

Start with a tidy table: one record per row, a clear header row, and the lookup key in the first column of the range. Then put the value you want to find in a separate cell. For a product-price lookup, use these steps:

Put product IDs in A2:A100, product names in B2:B100, and prices in C2:C100.
Enter the product ID to search for in E2. For example, suppose E2 contains P-104, and that row’s price in C is 18.50.
In F2, enter the formula below. The table range starts at column A, so its price column is the third column within the range.

With the layout described above, the formula returns 18.50 when P-104 is on a row whose price is 18.50:

=VLOOKUP(E2,A2:C100,3,FALSE)

The result appears in F2. If you want to look up other IDs, change E2 or copy the formula down a column; when copying, use a fixed table range such as $A$2:$C$100 so the range does not shift. Keep the lookup cell relative if each copied row should use its own ID.

Understand the VLOOKUP Arguments

The function has four arguments, in this order: VLOOKUP(search_key, range, index, is_sorted). The search key is the value to find, the range is the table to search, the index is the number of the return column counted from the range’s left edge, and the final argument chooses exact or approximate matching.

Search key: This can be a cell reference such as E2 or a value such as a number or text code.
Range: The first column of this range must contain the values VLOOKUP will search. The range must also include the column holding the answer.
Index: This is a position within the range, not a sheet column letter. In A2:C100, A is 1, B is 2, and C is 3.
Match setting: Use FALSE for an exact match in most ordinary lookup tasks. TRUE requests an approximate match.

It is easy to confuse the column index with a sheet column number. If your range begins at D and extends through F, F is index 3, not index 6. If you insert or rearrange columns, check that the index still points to the intended field.

Exact and Approximate Matches

For IDs, names, product codes, and other values that should match precisely, use FALSE as the fourth argument. An exact lookup returns the matching value; if there is no match, it normally returns #N/A. Exact match does not mean text capitalization must be identical: VLOOKUP in Sheets is generally not case-sensitive.

TRUE requests an approximate match. This is useful for banded ranges, such as assigning a shipping category based on weight thresholds, but the first column must be sorted in ascending order for the result to be dependable. In approximate mode, Sheets can return the value associated with the closest lower key when it does not find an exact one. Leaving out the fourth argument also selects approximate matching, so specifying FALSE is a safer habit when you need an exact match.

For example, a sorted table of minimum score thresholds and grades can use approximate matching to place a score into the correct band. Do not use this approach on an unsorted list of customer IDs: the returned result may be incorrect without an obvious error. When the desired rule is “find this exact key,” choose FALSE.

Useful Examples and Variations

The return column can contain text as well as numbers. Suppose A2:A100 contains employee IDs, B contains names, and C contains department names. With the employee ID in E2, use index 3 to retrieve the department. If the table begins in a different column, count the index from that new left edge.

With employee IDs in A2:A100, names in B2:B100, departments in C2:C100, and the requested ID in E2, this formula returns the matching department:

=VLOOKUP(E2,A2:C100,3,FALSE)

To return a name from that same table, change the index to 2. If you need several fields, you can place separate VLOOKUP formulas in adjacent result cells, using the relevant index for each. For a small form, this can be straightforward; for a larger report, consider whether XLOOKUP or a different table design will be easier to maintain.

Another useful adjustment is to lock the table range before filling a formula down. With the same product list in A2:C100 and each row’s search key in E2, this version keeps the lookup table fixed when copied, while E2 can change to E3, E4, and so on:

=VLOOKUP(E2,$A$2:$C$100,3,FALSE)

Fix Common VLOOKUP Problems

#N/A: The exact key may not exist in the first column, or the key and table values may differ in an unnoticed way. Check for leading or trailing spaces, inconsistent punctuation, and numbers stored as text in one place but numeric values in another. Also confirm that the range begins with the lookup-key column.

#REF!: The index may be larger than the number of columns in the selected range. If the range is A2:C100, its largest valid index is 3. Make sure the range includes the return column you intend to use.

Wrong value: Check whether the final argument is TRUE or omitted, especially if your data is not sorted. Confirm the index is counted from the start of the range, and remember that an exact lookup returns the first matching row if the lookup key appears more than once. Duplicate keys can therefore make the result seem unexpected.

Formula displays as text: Check that the cell is not formatted as plain text and that the formula begins with an equals sign. Re-entering the formula after changing the cell format can help. Also verify that the spreadsheet’s locale uses commas as argument separators; some locales use semicolons, so a formula copied from another locale may need that adjustment.

If you want a friendlier message when a key is missing, wrap the lookup in IFNA. With product IDs in A2:A100, prices in C2:C100, and the requested ID in E2, this displays “Not found” instead of the usual missing-match error:

=IFNA(VLOOKUP(E2,A2:C100,3,FALSE),"Not found")

IFNA handles the no-match result without masking every possible formula error. That is usually more helpful than using a broad error-catching function, because an invalid column index or a broken reference can still signal a problem worth fixing.

When to Use XLOOKUP or INDEX and MATCH

VLOOKUP is a reasonable choice when the key is already in the leftmost column and the table is simple. If the return data is to the left of the key, however, VLOOKUP cannot retrieve it by changing the index. Google Sheets’ XLOOKUP can use separate lookup and result ranges, making leftward lookups more direct.

With product IDs in B2:B100, prices in A2:A100, and the requested ID in E2, this XLOOKUP searches the IDs and returns the price from the column to their left. If there is no match, it displays “Not found”:

=XLOOKUP(E2,B2:B100,A2:A100,"Not found")

INDEX with MATCH is another flexible option. MATCH finds a position in the key range, and INDEX returns the value at that position in the result range. With IDs in A2:A100, prices in C2:C100, and the lookup key in E2, the formula returns the corresponding price:

=INDEX(C2:C100,MATCH(E2,A2:A100,0))

The zero in MATCH requests an exact match. This approach can be useful in existing Sheets or when you want lookup and result ranges specified separately. It is not necessary to replace every working VLOOKUP, though; use the function that makes the sheet’s logic clearest to its users.

Keep Lookup Tables Reliable

Good source data matters as much as the formula. Use a consistent format for keys, avoid blank rows inside the lookup table where practical, and make sure each key identifies the record you expect. If a key can appear more than once, decide which row should count or make the key unique by combining relevant fields in the source data.

For a table that will grow, you may choose a broader range than the rows currently filled, but an unnecessarily huge range can make a workbook harder to inspect. A fixed range is easier to understand and may be more efficient than searching entire columns in a large sheet. If you update the table regularly, remember to expand the range when new records fall outside it.

VLOOKUP works in Google Sheets in a web browser. Excel also has VLOOKUP with broadly similar arguments, but this tutorial’s examples and interface refer to Sheets; other spreadsheet programs may differ in features, separators, or behavior. In either program, verify the match setting and the range’s first column before relying on a returned value.

Frequently Asked Questions

Why does VLOOKUP return #N/A in Google Sheets?
Usually the exact key is not present in the range’s first column, or its stored value differs from the search key. Check for spaces, different data types, and an incorrect range start. If a missing match is expected, IFNA can show a custom message.

Can VLOOKUP search to the left?
No. VLOOKUP searches the first column in its range and returns a value from a column to the right. Use XLOOKUP with separate lookup and result ranges, or use INDEX and MATCH, when the result is to the left.

What does the number 3 mean in a VLOOKUP formula?
It means return the third column within the selected range. It is not necessarily column C on the sheet. For a range beginning in D, the third column is F.

Should I use TRUE or FALSE for VLOOKUP?
Use FALSE for an exact match, which is the usual choice for IDs, names, and codes. Use TRUE only for a deliberate approximate lookup, and sort the first column in ascending order.

Does VLOOKUP return the last matching row?
No. When exact matching finds duplicate keys, VLOOKUP returns the first matching entry. If duplicates are possible, clean or redesign the key data so each lookup identifies the intended record.

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.