Google Sheets VLOOKUP: How to Find and Return Data

Google Sheets VLOOKUP searches the first column of a range for a value and returns a value from another column in the same row. For an exact match, use =VLOOKUP(E2,A2:B4,2,FALSE); with product IDs in A2:A4, product names in B2:B4, and the ID to find in E2, this returns the matching product name.

What VLOOKUP Does in Google Sheets

VLOOKUP is short for vertical lookup. It is useful when a table is arranged with the value you want to search for in its leftmost column and the information you want to retrieve in a column to its right. For example, an order sheet might contain product codes in one column and prices in another. A lookup formula can find the code and return its price without requiring you to scan the table manually.

The function returns information from the same row as the matching value. It does not rearrange the source table or create a new table of results. If the lookup value appears more than once in the first column, VLOOKUP returns the first matching row it encounters. That behavior matters when a dataset contains duplicate IDs, names, or other keys: the first match may not be the record you intended.

VLOOKUP is available in Google Sheets in a web browser. It is a spreadsheet formula, not a special command in a menu, so you enter it in a cell just as you would enter other formulas. Google Sheets saves formula changes as part of the spreadsheet.

VLOOKUP Syntax and Its Four Arguments

The general form is =VLOOKUP(search_key, range, index, [is_sorted]). The square brackets in the function description indicate that the last argument is optional; do not type those brackets into a formula. Each argument controls a different part of the lookup:

search_key: The value to find, such as a product code, a name, or a cell reference containing one.
range: The table area to search. The first column of this range must contain the lookup values.
index: The position of the result column within the selected range. The first column is 1, the second is 2, and so on.
is_sorted: Choose FALSE for an exact match or TRUE for an approximate match. If omitted, Google Sheets uses TRUE, so it is safer to specify the choice deliberately.

The index is counted from the left edge of the range, not from the worksheet’s column A. For instance, if the range begins in column D, its first column still has index 1. VLOOKUP also cannot return a value from a column to the left of the search column within the selected range.

Build an Exact-Match Lookup

Exact matching is the right choice for most lookups involving unique identifiers, such as employee numbers, invoice IDs, and product codes. Here is a small example table: A2:A4 contains P-101, P-102, and P-103; B2:B4 contains Mug, Cable, and Stand; and E2 contains P-102. With that layout, enter this formula in F2:

=VLOOKUP(E2,A2:B4,2,FALSE)

The formula searches the first column of A2:B4 for the value in E2. It finds P-102 in the second row of the selected range and returns the value from the second column of that row, Cable. FALSE tells Sheets to require an exact match. If E2 instead contains an ID that is not in A2:A4, the formula returns #N/A.

To use the formula for many records, copy it down the result column. Relative references such as E2 will adjust to E3 and E4 as the formula moves. If you need the source table to remain fixed while copying, use absolute references. With IDs in E2:E20 and the source table in A2:B100, enter the formula in F2 and fill it down:

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

The dollar signs keep the lookup range fixed, while E2 remains relative so each row checks its own search value. You can type the dollar signs directly or select a reference while editing and use the keyboard shortcut appropriate to your device. A range reference can also cover whole columns, but a bounded range can be easier to audit in a large or complex sheet.

Exact Match Versus Approximate Match

With FALSE, the function looks for an exact match and does not require the first column to be sorted. This is usually the dependable option for IDs and labels. A common misconception is that VLOOKUP automatically performs an exact search when its last argument is left out. In Google Sheets, the default is TRUE, which requests an approximate match instead.

Approximate matching is useful for threshold tables, such as assigning a category based on a score range. With scores 0, 60, and 80 in A2:A4 and labels Low, Medium, and High in B2:B4, the first column must be sorted in ascending order. With the score to classify in E2, use:

=VLOOKUP(E2,A2:B4,2,TRUE)

For a score of 72, the result is Medium: the formula uses the largest threshold that is less than or equal to 72. For approximate lookups, sorting is essential; an unsorted first column can produce an incorrect result. A value below the smallest threshold may return #N/A. Use this mode only when the table is designed as ordered breakpoints and that behavior is wanted.

Choose a Range That Returns the Right Column

The search column must be the first column in the range, and the return column must be to its right. Suppose a sheet has descriptions in A, codes in B, and prices in C. A lookup by code cannot use the entire A:C range as written, because VLOOKUP would search descriptions in column A. One option is to set the range to B:C, where codes are first and prices are second. With a code in E2 and codes and prices in B2:C100, the formula is:

=VLOOKUP(E2,B2:C100,2,FALSE)

This returns the price for an exact code match. When designing a new lookup table, putting the key column first often makes formulas simpler. If changing the table layout is inconvenient, an alternative lookup function may be a better fit.

Be careful when adding or moving columns inside a range. The index is a number, so inserting a column can change which field that number refers to. After changing a source table, check that the formula still points to the intended return column. A clearly labeled header row and a modestly sized range make the lookup easier to review.

Common VLOOKUP Problems and Fixes

When a formula does not return the expected result, first check the lookup value, range, and match mode. The most frequent errors have straightforward causes:

#N/A: No exact match was found, or an approximate lookup could not find a usable threshold. Check spelling, spaces, the selected range, and whether the lookup value is actually present.
#REF!: The index asks for a column beyond the number of columns in the range. For example, an index of 3 cannot return a field from a two-column range.
Unexpected result: The formula may be using TRUE or omitting the last argument, so it is doing an approximate lookup. Specify FALSE for an exact match.
Wrong record: The first-column key may not be unique. VLOOKUP returns the first matching row, not every matching row.
Numbers that do not match: A value stored as text may not behave like the same digits stored as a number. Check formatting and the underlying values, especially for codes with leading zeros.

Extra spaces are another frequent source of apparent mismatches. A cell containing a space after a code is not necessarily the same text as a cell without it. Inspect the source and lookup cells, or clean imported data before relying on a lookup. Be cautious about stripping leading zeros from identifiers: a code such as 0042 may be a label rather than the number 42, and converting it can change the key.

If you want a custom message instead of #N/A, wrap the lookup in IFNA. With the same product table in A2:B4 and the search value in E2, enter:

=IFNA(VLOOKUP(E2,A2:B4,2,FALSE),"Not found")

This displays Not found when the lookup produces #N/A. IFNA is more targeted than IFERROR, which can also conceal other formula problems that you may want to notice and fix.

Alternatives and When to Use Them

VLOOKUP remains a practical choice for a straightforward search in the leftmost column of a table. It is widely understood, easy to copy down, and suitable when the return field is to the right. Its main structural limitation is that the search column has to be the first column in the selected range.

INDEX and MATCH can separate the search range from the return range, which allows a lookup to return a value from a column on either side. With product codes in B2:B100, prices in A2:A100, and the code to find in E2, this formula returns the corresponding price from the column on the left:

=INDEX(A2:A100,MATCH(E2,B2:B100,0))

MATCH with 0 requests an exact match; INDEX returns the value at the matching position in the price range. This approach is useful when the source layout cannot be changed, though it may be less familiar to readers who know only VLOOKUP.

For a different task—returning all rows that meet a condition rather than a single value—FILTER may be more appropriate. With product codes in A2:A100, product names in B2:B100, and the requested code in E2, use:

=FILTER(B2:B100,A2:A100=E2)

This returns matching names as an array and can return more than one result if the code appears in multiple rows. It is not a drop-in replacement when you need one result from the first matching row. Pick the function based on whether the job is one lookup result, a leftward lookup, or a set of matching rows.

Frequently Asked Questions

Does Google Sheets VLOOKUP use exact match by default?

No. If you leave out the last argument, Google Sheets defaults to approximate matching. For ordinary exact lookups, include FALSE explicitly so the formula does not depend on that default.

Can VLOOKUP return a value from a column to the left?

No. VLOOKUP searches the first column of its range and returns from a column to its right. Use INDEX with MATCH, or reorganize the lookup range so the search column comes first.

Why does VLOOKUP return the wrong matching row?

Check whether the formula is using approximate match and whether the first column is sorted as required. If the formula uses FALSE, look for duplicate keys: VLOOKUP returns the first match, so repeated values can make a later row’s result inaccessible through that formula.

Can VLOOKUP search for part of a word?

VLOOKUP is generally used to match a complete key, rather than to find a phrase anywhere inside a cell. For substring searches or more flexible text conditions, consider a different approach, such as FILTER with a text-search condition, and confirm that it returns the result shape you need.

How do I copy a VLOOKUP formula down without changing its source range?

Make the table range absolute with dollar signs, while leaving the lookup cell reference relative. For example, a fixed range such as $A$2:$B$100 stays in place as the formula is copied, while E2 changes to E3 on the next row.

Practical Checklist Before Sharing a Lookup Sheet

Before relying on a VLOOKUP in a shared spreadsheet, verify that the key column is the first column of the chosen range, the index points to the intended return field, and the match mode is explicit. Confirm whether keys are unique, test a value you know should match and one you know should not, and check that copied formulas use fixed source ranges where needed. These small checks help prevent silent errors, especially when a table is updated by several people or imported from another system.

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.