How to Use VLOOKUP in Google Sheets: Comprehensive Guide

VLOOKUP ("vertical lookup") searches down the first column of a range for a key and returns a value from another column in the same row. It is the function most people learn first when they need to pull data from one table into another: prices by product ID, department by employee name, tax rate by country.

VLOOKUP is also easy to get wrong. The most common problems are an approximate match that silently returns the wrong row, #N/A errors caused by invisible spaces, and a lookup column that sits to the right of the value you want. This guide covers the syntax, the mistakes, and the patterns that solve real spreadsheets.

Syntax

=VLOOKUP(search_key, range, index, [is_sorted])

search_key: The value to look for. It is matched against the first column of range.
range: The table to search. The column holding the key must be the left-most column of this range.
index: The column number within range (not within the sheet) to return a value from. 1 is the key column itself, 2 is the next column, and so on.
is_sorted [Optional]: TRUE (the default) means approximate match and requires the first column to be sorted ascending. FALSE means exact match. Use FALSE unless you specifically want an approximate match.

Quick example: exact match

Suppose A2:C6 holds a product list:

   A (SKU)   B (Product)   C (Price)
2  A-100     Notebook      4.50
3  A-101     Pen           1.20
4  A-102     Stapler       9.99
5  A-103     Folder        0.80
6  A-104     Marker        2.30

To get the price of SKU A-102:

=VLOOKUP("A-102", A2:C6, 3, FALSE)

Result: 9.99. The key is found in column A (the first column of the range), and 3 asks for the third column of the range, which is the price. In practice the key usually sits in a cell, so you would write =VLOOKUP(E2, $A$2:$C$6, 3, FALSE) and drag it down. The $ signs lock the range so it does not move when you copy the formula.

Exact match vs. approximate match

The fourth argument decides how the key is matched, and it is the source of most VLOOKUP bugs.

FALSE: exact match. Returns the first row whose first column equals the key. If nothing matches, you get #N/A. Matching is case-insensitive and supports wildcards (* for any text, ? for one character), so =VLOOKUP("A-1*", A2:C6, 2, FALSE) returns the first SKU starting with A-1.

TRUE (or omitted): approximate match. Returns the row with the largest value that is less than or equal to the key. This is useful for ranges such as grade bands or tax brackets, but only when the first column is sorted ascending. If it is not sorted, the result is unpredictable and Sheets will not warn you.

A grading example, with the thresholds in F2:G6:

   F (Min score)   G (Grade)
2  0               F
3  60              D
4  70              C
5  80              B
6  90              A

=VLOOKUP(84, F2:G6, 2, TRUE)

Result: B. 84 is not in the table, so VLOOKUP takes the largest threshold not exceeding it (80). If you had forgotten the fourth argument on a product-ID lookup, the same behavior would return the wrong product without any error. That is why you should write FALSE explicitly for lookups by ID or name.

Common errors and how to fix them

#N/A: "Did not find value in VLOOKUP evaluation". The key does not exist in the first column. Before assuming the data is missing, check for these causes:

Extra spaces. "A-102 " is not "A-102". Wrap the key in TRIM: =VLOOKUP(TRIM(E2), A2:C6, 3, FALSE).
Text vs. number. The number 1001 and the text "1001" do not match. Convert with VALUE() or TO_TEXT() so both sides have the same type. A left-aligned number in a cell is a hint that it is stored as text.
Range does not start at the key column. If your keys are in column B, the range must start at column B (B2:D6), not A2:D6.
Approximate match on unsorted data. Switch the last argument to FALSE.

#REF!: "VLOOKUP evaluation has out of range column index value". index is larger than the number of columns in range. A range of A2:C6 has three columns, so index can be at most 3.

#VALUE!. index is less than 1 or is not a number.

To show something friendlier than an error, wrap the formula in IFERROR:

=IFERROR(VLOOKUP(E2, $A$2:$C$6, 3, FALSE), "Not found")

Use this with care: it hides every error, including a wrong index. While you build the formula, leave IFERROR off so you can see what is actually going wrong.

Patterns that solve real problems

Look up several columns at once

VLOOKUP only returns one column per call, but you can give index an array and wrap the formula in ARRAYFORMULA to return several columns side by side:

=ARRAYFORMULA(VLOOKUP(E2, A2:C6, {2, 3}, FALSE))

This returns both the product name and the price in two adjacent cells.

Look up to the left

VLOOKUP can only return columns to the right of the key. To look up a SKU from a product name (the SKU is to the left), reorder the columns with an array literal:

=VLOOKUP("Stapler", {B2:B6, A2:A6}, 2, FALSE)

Result: A-102. For anything more complex, INDEX and MATCH (or XLOOKUP) are cleaner, as covered below.

Look up on two criteria

VLOOKUP matches a single key. To match on two columns, such as first name and last name, build a combined key on the fly:

=ARRAYFORMULA(VLOOKUP(E2 & F2, {A2:A100 & B2:B100, C2:C100}, 2, FALSE))

The array literal creates a two-column table: the concatenated key in the first column and the value to return in the second.

Look up from another sheet or file

To search a different tab, prefix the range with the sheet name: =VLOOKUP(A2, Prices!A:C, 3, FALSE). To search a different spreadsheet file, wrap the range in IMPORTRANGE:

=VLOOKUP(A2, IMPORTRANGE("spreadsheet_url", "Prices!A:C"), 3, FALSE)

The first time you use this, Sheets asks you to allow access between the two files.

Fill a whole column with one formula

Instead of dragging the formula down, wrap it in ARRAYFORMULA once in the header row and let it fill the column:

=ARRAYFORMULA(IF(E2:E="", "", VLOOKUP(E2:E, $A$2:$C$6, 3, FALSE)))

The IF keeps blank rows empty so you do not fill the column with #N/A.

VLOOKUP vs. INDEX/MATCH vs. XLOOKUP

Looking left of the key: VLOOKUP cannot (without an array trick). INDEX/MATCH and XLOOKUP can.
Inserting a column: VLOOKUP breaks, because the index number is hard-coded. INDEX/MATCH and XLOOKUP keep working because they reference the return column directly.
Default match type: VLOOKUP defaults to approximate, which is risky. INDEX/MATCH and XLOOKUP default to exact.
Built-in "not found" value: only XLOOKUP has one. VLOOKUP and INDEX/MATCH need IFERROR.
Familiarity: VLOOKUP is the one most people already know.

VLOOKUP is fine for simple, stable tables and is the one your colleagues will recognize. Prefer XLOOKUP for new work because it defaults to exact match and can look in any direction. Use INDEX/MATCH when you want a lookup that survives column changes without relying on XLOOKUP.

Video Example

Frequently asked questions

Is VLOOKUP case-sensitive? No. "apple" and "APPLE" match. For a case-sensitive lookup, use INDEX/MATCH with EXACT.

What happens when there are duplicate keys? VLOOKUP returns the first match from the top and ignores the rest. If you need all matches, use FILTER.

Can VLOOKUP return a whole row? Yes, with ARRAYFORMULA and a list of indexes ({1,2,3}), as shown above. For a full row or all matches, FILTER is usually simpler.

Why does VLOOKUP return the wrong value instead of an error? Almost always because the fourth argument was omitted, so Sheets used an approximate match on an unsorted column. Add FALSE.

Related Functions

XLOOKUP: The modern replacement. Exact match by default, can look in any direction.
HLOOKUP: The same lookup, searching across the top row instead of down the first column.
INDEX: Retrieve a value from a specified position in a range.
MATCH: Find the position of a value in a range.
IFERROR: Replace errors with a custom value.
ARRAYFORMULA: Apply a formula to a whole column at once.

VLOOKUP