Google Sheets VLOOKUP: Formula, Examples, and Fixes

In Google Sheets, VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. With a lookup ID in E2 and a table in A2:C100, an exact-match formula that returns the third column is =VLOOKUP(E2, A2:C100, 3, FALSE).

What VLOOKUP does

VLOOKUP stands for vertical lookup: it checks a column from top to bottom, finds the first matching value, and returns a related value from that row. It is useful when a sheet contains a reference table, such as product IDs and prices, employee IDs and departments, or postal codes and regions. Rather than searching the table manually, you provide the value to find and the column containing the answer.

The function has four arguments: the value to search for, the table range, the number of the return column within that range, and 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 Google Sheets, the fourth argument is optional, but including it makes the intended match behavior clear.

Search key: the value VLOOKUP should find, such as a product code in E2.
Range: the table containing both the search values and the answers.
Index: the position of the answer column within that range, starting at 1.
Is sorted: FALSE requests an exact match; TRUE requests an approximate match and expects the first column to be sorted.

A basic exact-match example

Imagine a price list with product IDs in column A, names in column B, and prices in column C. Rows 2 through 5 contain P-104, Notebook, 2.50; P-208, Pen, 1.20; P-311, Folder, 3.00; and P-417, Eraser, 0.75. If E2 contains P-311, the formula below looks for that code and returns the value from the third column of the table.

With the product table in A2:C5 and the requested product ID in E2, enter =VLOOKUP(E2, A2:C5, 3, FALSE). The result is 3.00, the price in the same row as P-311.

FALSE is important for most everyday lookups. It tells Sheets to find the same value, rather than treating nearby values as acceptable. If there is no exact match, the result is usually #N/A. A lookup key must be in the leftmost column of the selected range: VLOOKUP cannot find a price in column C and then return a product name from column B.

How to enter and fill the formula

You can type a formula directly into a cell or use the formula bar. To create the example above:

Put the lookup key, such as a product ID, in a cell such as E2.
Check that the table’s lookup keys are in the leftmost column of the range you plan to use.
Select the cell where you want the answer, type the formula with FALSE for an exact match, and press Enter.
Change the value in E2 to check another item. The returned value updates automatically.

If you copy a formula down a column, an ordinary range reference may move as the formula moves. To keep the source table fixed, add dollar signs to its row and column references. With product IDs in E2:E20 and the source table in A2:C100, enter this in F2 and fill it down: =VLOOKUP(E2, $A$2:$C$100, 3, FALSE). E2 changes to E3, E4, and so on, while the table remains A2:C100.

In a Google Sheets web browser, you can drag the small blue square at the lower-right corner of a selected cell to copy its formula into neighboring cells. You can also copy and paste. If a formula is intended for just one lookup, you do not need to lock the range.

Exact match and approximate match

For IDs, names, order numbers, and most lookups where each key should identify a specific record, use FALSE. Exact matching avoids accidentally returning a result for a nearby number or text value. It also does not require the lookup column to be sorted.

Approximate matching can be useful for thresholds, such as assigning a band to a score or choosing a tax-rate bracket from a table. With TRUE, VLOOKUP looks for an exact match or, when there is not one, the closest smaller value in the first column. That column must be sorted in ascending order for the result to be reliable. If the key is below the smallest threshold, the formula can return #N/A.

For example, suppose A2:B5 contains minimum scores and labels: 0, Bronze; 60, Silver; 80, Gold; and 95, Platinum. The thresholds in A2:A5 are sorted ascending. With a score of 84 in E2, enter =VLOOKUP(E2, A2:B5, 2, TRUE). The result is Gold, because 80 is the largest threshold not greater than 84. If the lookup table is unsorted, use FALSE for exact-match tasks or sort the threshold table before relying on approximate results.

Column positions and lookup direction

The index argument is relative to the selected range. If the range starts in column B, then B is index 1, C is index 2, and D is index 3. This is a frequent source of mistakes: a formula can be syntactically valid yet return the wrong field because its index counts from the range’s first column, not from column A.

VLOOKUP only returns values to the right of its lookup column. For instance, if product names are in A and IDs are in B, you cannot select B:A and ask VLOOKUP to return the name: the lookup column must still be first, and the range does not work in reverse. You can rearrange the data, or use another lookup method that accepts separate search and result ranges.

Also note that VLOOKUP returns the first matching row when the lookup column contains duplicate keys. If a product ID appears more than once, it will not automatically combine the matching rows or return every match. Make lookup keys unique when one result per key is expected; use a filtering approach when you need multiple records.

Common errors and how to fix them

A #N/A error usually means an exact match was not found. Check for spelling differences, leading or trailing spaces, and mismatched data types. A number stored as text may not match the same digits stored as a numeric value. Also verify that the lookup key is in the first column of the selected range and that the value actually exists there.

A #REF! error can mean the column index is larger than the number of columns in the range. For example, an index of 4 cannot return a value from a three-column range. A #VALUE! error can occur when the index is less than 1. Check both the selected range and the index before changing the data.

If a missing key should display a friendly message rather than an error, wrap the lookup in IFNA. With IDs in A2:A100, prices in C2:C100, and the requested ID in E2, use =IFNA(VLOOKUP(E2, A2:C100, 3, FALSE), "No matching product"). The formula returns the matching price when one exists and No matching product when VLOOKUP returns #N/A. IFNA handles that specific missing-value error; it does not hide other formula problems such as an invalid column index.

When a copied formula gives the same answer on every row or begins returning errors, inspect its references. The search-key reference should usually change by row, while the lookup table should remain fixed. Finally, check the spreadsheet’s locale if commas in a pasted formula are rejected: some locale settings use semicolons as argument separators. In Google Sheets, review this under File > Settings.

Alternatives when VLOOKUP is not a good fit

Google Sheets also has XLOOKUP, which lets you specify the lookup column and result column separately. That makes it possible to return a value from a column to the left as well as to the right, and avoids counting a return-column index. With IDs in A2:A100, names in B2:B100, and the requested ID in E2, this returns the corresponding name or a message if none is found: =XLOOKUP(E2, A2:A100, B2:B100, "Not found").

Another option is INDEX with MATCH. MATCH finds the position of a key, and INDEX returns the value at that position in the result range. With IDs in B2:B100, names in A2:A100, and the requested ID in E2, enter =INDEX(A2:A100, MATCH(E2, B2:B100, 0)). The zero asks MATCH for an exact match, and the separate ranges allow the answer to be on either side of the lookup column.

VLOOKUP remains a reasonable choice for simple tables where the key is on the left and the answer is to its right. XLOOKUP or INDEX/MATCH can be easier to maintain when columns may be rearranged or the answer is to the left. For tasks that need every matching row rather than one result, consider a function such as FILTER instead of expecting VLOOKUP to return multiple results.

Google Sheets and Excel differences

The basic VLOOKUP syntax and the examples here work in both Google Sheets and Microsoft Excel. This article uses Google Sheets in a web browser; the menus, sharing features, and some available functions differ between the programs. In particular, XLOOKUP is available in Google Sheets and in current Microsoft 365 Excel, but function availability can depend on the spreadsheet product and version.

Google Sheets may use different argument separators depending on the file’s locale, so a formula copied from a comma-using example may need semicolons in some settings. The function name and lookup logic remain the same. If you collaborate with people in different locales, check the spreadsheet’s settings and test formulas after changing locale-related options.

Frequently Asked Questions

What is the simplest VLOOKUP formula in Google Sheets?
Use the search key, the table range, the return-column index, and FALSE for an exact match. For a key in E2 and a three-column table in A2:C100, the formula is =VLOOKUP(E2, A2:C100, 3, FALSE).

Does VLOOKUP in Google Sheets search left?
No. The values to search must be in the leftmost column of the selected range, and VLOOKUP returns a value from a column to the right. Use XLOOKUP or INDEX with MATCH when the answer is to the left.

Why does my VLOOKUP return #N/A?
With an exact match, #N/A generally means the key was not found. Check that the key exists in the first column of the range, and look for extra spaces, spelling differences, or a number-versus-text mismatch.

Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for exact matches, which is the right choice for most IDs and named records. Use TRUE for an approximate threshold lookup only when the first column is sorted ascending and you want the nearest lower threshold when there is no exact match.

Can VLOOKUP return more than one matching row?
No. VLOOKUP returns a single result from the first matching row. If you need all rows matching a condition, use a filtering function such as FILTER or another approach designed to return multiple results.

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.