Does Google Sheets Have VLOOKUP? How to Use It

Yes. Google Sheets has the VLOOKUP function, which searches down the first column of a range and returns a value from another column in the same row. For the common case of an exact match, use =VLOOKUP(A2, Products!A:C, 3, FALSE): it looks for the value in A2 in the first column of Products!A:C and returns the matching row’s value from the range’s third column.

What VLOOKUP Does in Google Sheets

VLOOKUP is short for vertical lookup. It is useful when a table has one record per row and you want to find a detail associated with a key, such as a product code, employee ID, customer name, or postal code. The function searches vertically through the table’s leftmost column, finds a matching key, then returns a value from a column to its right.

For example, a product list might have product codes in column A, names in column B, and prices in column C. If another sheet contains a product code, VLOOKUP can retrieve its price without manually scanning the list. The source data can be on the same sheet or another tab in the same spreadsheet.

VLOOKUP is a standard Google Sheets function, not an add-on. It is available in Sheets in a web browser, and the same basic syntax works in the Sheets mobile apps. Excel also has VLOOKUP, though menu locations and some newer alternative functions can differ between the two spreadsheet programs.

VLOOKUP Syntax and Arguments

The function takes a search value, a rectangular range, a column number, and an optional match-mode setting. Its syntax is VLOOKUP(search_key, range, index, [is_sorted]). The square brackets indicate that the last argument is optional; they are not typed into the formula.

search_key: The value to find, often a cell reference such as A2.
range: The table to search, with the lookup values in its first column.
index: The number of the return column counted from the left edge of that range. The first column is 1, not 0.
is_sorted: TRUE requests an approximate match; FALSE requests an exact match. If omitted, Sheets uses TRUE, so it is safer to specify FALSE for ordinary lookups.

The column number is relative to the selected range, not the worksheet’s column letters. If the range is B:D, then B is index 1, C is index 2, and D is index 3. The range must include both the key column and the column containing the answer.

Example: Return a Product Price

Suppose a Products tab has product codes in A2:A100, names in B2:B100, and prices in C2:C100. On an order sheet, A2 contains the code to look up. The range starts in column A, so the price column is the third column in the range.

With the lookup code in Order!A2 and product codes, names, and prices in Products!A2:C100, enter =VLOOKUP(A2, Products!A2:C100, 3, FALSE) in the order sheet’s result cell. If A2 contains P-104 and that code’s price is 12.5, the formula returns 12.5. Using FALSE makes Sheets look for the code itself rather than a nearby value.

To reuse the formula down many order rows, keep the product table fixed while allowing the search cell to change. With order codes in A2:A20 and the product table in Products!A2:C100, enter =VLOOKUP(A2, Products!$A$2:$C$100, 3, FALSE) in the first result row, then fill or copy it down. The dollar signs lock the lookup range, while A2 adjusts to A3, A4, and so on.

In Sheets, you can type the formula directly into a cell or select a cell and enter it in the formula bar. A tab name containing spaces needs single quotes around it: for example, use 'Product List'!A2:C100 as the range reference. The reference syntax is part of the formula; it does not require a special VLOOKUP menu.

Exact Match Versus Approximate Match

For IDs, names, codes, and most everyday lookups, use FALSE for an exact match. If no matching key exists, the usual result is #N/A. That is useful information: it means Sheets did not find a matching value under the chosen match mode, though a difference in spaces, data type, or spelling may be the underlying cause.

TRUE requests an approximate match. In that mode, the first column of the lookup range must be sorted in ascending order. Sheets finds an exact value when available; otherwise, it returns the row for the largest first-column value that is less than or equal to the search key. This can suit tier tables, such as tax brackets or shipping thresholds, but a badly sorted table can produce a plausible-looking yet incorrect answer.

For instance, a discount table could list minimum purchase amounts of 0, 50, and 100 in ascending order, with a discount rate beside each amount. A purchase of 75 can use the 50 threshold. If the lookup table is not sorted as required, the approximate result is not dependable. When you do not specifically need threshold matching, choose FALSE and avoid this sorting condition.

Important Limits and Behavior

VLOOKUP only searches the first column of its range, and it can only return a value to the right of that column. If the lookup key is in column C but the answer is in column A, rearranging the source table may not be practical. In that case, consider XLOOKUP or an INDEX and MATCH combination instead.

If the first column contains duplicate keys, VLOOKUP returns the value associated with the first matching row it encounters, not every matching row. It is not a way to total or list all records for a key. For multiple matching rows, a function such as FILTER is usually a better fit; for a sum across matching records, SUMIF or SUMIFS may be more suitable.

Text matching with VLOOKUP is not case-sensitive, so codes that differ only by uppercase and lowercase letters are not reliably distinguished. Also check whether a key is stored as text in one place and as a number in another. A value that looks the same on screen can behave differently if its underlying type or formatting differs.

Common VLOOKUP Errors and Fixes

#N/A commonly means no exact match was found. Check for extra spaces, punctuation differences, leading zeros, or number-versus-text mismatches. If the source data may contain stray spaces, you can clean it or create a helper column with normalized values. Do not switch to approximate matching just to hide an error; that can return the wrong record.

#REF! can appear when the index asks for a column beyond the width of the selected range. For example, an index of 4 cannot return a column from a three-column range. Recount columns from the range’s left edge. A range that begins at B has B as index 1, even though B is the second worksheet column.

A copied formula may start returning wrong values if its range shifts as it is filled down. Use dollar signs to make the range absolute, as in the earlier example. Another common issue is forgetting the final FALSE argument: because approximate matching is the default, an omitted argument can lead to surprising results, especially when the first column is not sorted.

If you wrap a formula to show a friendly message for missing matches, with lookup values in A2:A20 and a source table in Products!A2:C100, use =IFNA(VLOOKUP(A2, Products!$A$2:$C$100, 3, FALSE), "Not found"). This displays Not found instead of #N/A when no exact match exists. Keep in mind that the message can make missing data less visible, so it is worth checking why a match failed rather than suppressing errors without review.

VLOOKUP Compared With Other Lookup Options

VLOOKUP remains a convenient choice when the key is in the leftmost column and the desired answer is to its right. It is familiar to many spreadsheet users, easy to read in a simple table, and works well for single-result lookups. Its main structural limitation is that the search and return directions are fixed by the range.

XLOOKUP can search in one range and return a corresponding value from another, so the return column does not have to be to the right of the key column. It also provides an optional not-found result. For a lookup value in A2, keys in Products!A2:A100, and prices in Products!C2:C100, the Sheets formula is =XLOOKUP(A2, Products!A2:A100, Products!C2:C100, "Not found"). Check that the function is available in the spreadsheet environment you use; Google Sheets supports XLOOKUP, while availability and exact feature support can vary across other spreadsheet applications or older software.

INDEX and MATCH can also separate the return range from the lookup range and are useful when you need that flexibility or need to support software without XLOOKUP. FILTER is more appropriate when you want all rows that meet a condition rather than only the first matching result. Choose based on the shape of the data and whether you need one answer, multiple rows, or a threshold match—not simply on which function has the shortest formula.

Tips for Reliable Lookups

Use a unique, stable key such as a product ID instead of a name that may be edited or shared by multiple records.
Specify FALSE for exact matching unless you deliberately need an approximate threshold lookup.
Make the lookup range start with the key column and include the return column.
Use absolute references for a fixed source table when copying formulas down.
Check duplicate keys, sorting, blank cells, and text-versus-number differences when a result looks wrong.
Keep the source table’s column layout understandable. Inserting or moving columns can make a hard-coded index return a different field than intended.

VLOOKUP formulas recalculate as spreadsheet data changes, but they do not correct inconsistent source data. If a product code is duplicated or a price is recorded incorrectly, the function can faithfully return the wrong row’s value. For important reports, validate the key column and spot-check results against the underlying table.

Frequently Asked Questions

Is VLOOKUP free in Google Sheets?
VLOOKUP is a built-in function in Google Sheets and does not require a separate add-on. Access to Sheets itself is subject to the Google account or Workspace arrangements being used.

Why does Google Sheets VLOOKUP return #N/A?
With exact matching, #N/A means Sheets did not find the search key in the range’s first column. Check the selected range, spelling, spaces, leading zeros, and whether the key is stored as text or a number.

Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the range’s first column and returns from a column to its right. Use XLOOKUP or INDEX and MATCH when the return column is left of the lookup column.

Does VLOOKUP find every matching row?
No. It returns a value from the first matching row it finds. Use FILTER to return multiple rows that meet a condition, or a suitable aggregate function if you need a combined total.

Do I need to sort the table for VLOOKUP?
Not when you set the last argument to FALSE for an exact match. If you use TRUE or omit the argument, the first column must be sorted in ascending order for approximate matching to work as intended.

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.