VLOOKUP() in Google Sheets: How to Use It + Examples

VLOOKUP() is a Google Sheets function that searches for a value in a column and returns a matching value from another column in the same row. You give it something to find, tell it where to look, and it brings back the data you need.

It's most helpful when you're working with large datasets where finding and matching data manually would take too long. But any time you need to connect two tables of data using a shared value, VLOOKUP() is the go-to function.

In this guide, you'll learn its syntax, how to use it step by step, what causes it to give incorrect results, and when to opt for its alternatives like INDEX MATCH() or XLOOKUP().

What Is the Syntax of VLOOKUP() in Google Sheets?

The syntax of VLOOKUP() is:

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

Here: 

• search_key is the value you want to find

• range is the table where the search happens

• index is the column number to return the result from

• [is_sorted] defines whether you want an exact match or not

How Do You Do a VLOOKUP() in Google Sheets?

To do a VLOOKUP() in Google Sheets:

1. Decide what value you want to look up: This could be an ID, name, or any unique value.

2. Identify your table range: Make sure the column containing your lookup value is the first column in that range.

3. Choose what data you want returned: This is the column with the result (e.g., name, price, status).

4. Enter the VLOOKUP() formula in a new cell: Reference your lookup value, table range, and the column you want to return.

5. Use exact or approximate match (FALSE or TRUE): In most cases, you want an exact match to avoid incorrect results (more on this later).

If you have this table:

And you want to find the name for ID 102, use:

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

This tells Google Sheets to:

• Look for 102 in column A
• Return the value from column B
• Give an exact match

As a result, you will get Sarah.

Example of VLOOKUP(). Image by Author.

How VLOOKUP() Works (Step-by-Step Logic)

VLOOKUP() follows a simple vertical search process. It always starts with the first column of your selected range, then scans downward, one row at a time, until it finds the first match.

Once it finds that match, it stays in the same row. Then it moves across to the column you specified and returns that value.

Here’s how it works step by step:

• Starts in the first column of the range
• Searches down the column
• Finds the first matching value
• Moves across the same row
• Returns data from the specified column

Let’s say you want to find the job title for Employee ID E1007. For this, use the following formula:

=VLOOKUP("E1007", A:G, 5, FALSE)

VLOOKUP() in Google Sheets. Image by Author.

This formula:

• Finds E1007 in column A

• Moves across that same row

• Returns column 5 (Job Title) 

• Gives the result Software Engineer.

Note: VLOOKUP() can only search from left to right. If your lookup column is not the first column in the range, the function will not work. In this case, you have to rearrange your data or use a different function. We’ll get to that later.

Exact Match vs Approximate Match in VLOOKUP()

Exact match returns a result only when the value is identical. Approximate match returns the closest available value when an exact match is not found, usually based on sorted data.

In VLOOKUP(), this is controlled by the last argument. The last argument, FALSE or TRUE, is the one most people get wrong because TRUE looks harmless and isn't.

• FALSE means exact match only. If the value isn't there, you get #N/A, which is actually helpful. 
• TRUE returns the closest lower value when no exact match exists, which sounds like a fallback but is more accurately described as a silent wrong answer. It also assumes your data is sorted. If it isn't, the result is undefined. 

I'd say use TRUE only for tiered lookups, such as tax brackets or grade bands, where approximate is the correct behavior by design. For everything else, use FALSE.

Exact match (FALSE)

This tells VLOOKUP() “Only return a result if you find this exact value.” For example, this formula =VLOOKUP("E1005", A:G, 7, FALSE) looks for E1005, returns the salary from column 7, and gives the result 90000.

If the value does not exist, you get a #N/A error. 

Exact match in VLOOKUP(). Image by Author.

Approximate match (TRUE)

This tells VLOOKUP(), “If you don’t find an exact match, return the closest value below it.” That may sound helpful — sometimes it is, but sometimes it quietly gives you the wrong answer.

For example, E1011 does not exist in our dataset. The last employee ID is E1010. So if you use this formula =VLOOKUP("E1011", A:G, 7, TRUE), VLOOKUP() will not return an error. Instead, it would return the value for the closest lower match, which is E1010. The cell will display 88000.

So even though E1011 is missing, VLOOKUP() still gives you a result because TRUE allows an approximate match.

Approximate match in VLOOKUP(). Image by Author.

Now imagine the same data is out of order, and I enter the available employee ID in the formula, like this: =VLOOKUP("E1011", A:G, 7, TRUE).

This time, the formula returns an incorrect salary because the lookup column is not sorted, and the approximate match depends on sorted data.

VLOOKUP() approximate match throws an error due to unsorted list. Image by Author.

Common Errors in VLOOKUP() and How to Fix Them

Let’s look at some of the most common issues you may come across with VLOOKUP() and how to solve them: 

#N/A error

A #N/A error means VLOOKUP() could not find the value you asked for.

Suppose you have this formula: =VLOOKUP("E9999", A:G, 7, FALSE)

 If E9999 is not in the first column of the range, the formula returns #N/A.

This can also happen if the value is typed differently in the sheet, such as with extra spaces or text formatting issues.

To fix it:

• Make sure the value exists in the first column of the range

• Use the correct cell reference instead of typing the value manually when possible

• Add quotation marks if you hardcode a text value

• Remove leading or trailing spaces from the data

• Use FALSE only when you need an exact match 

Wrong column index

The column index tells VLOOKUP() which column to return from within the selected range. If the index number is too large, the formula returns #REF!.

This =VLOOKUP("E1005", A:G, 8, FALSE) returns #REF! because the range A:G has only 7 columns.

Wrong column index throws an error. Image by Author.

You can also get the wrong result if the column number is valid but points to the wrong column.

For example:

=VLOOKUP("E1005", A:G, 5, FALSE)

This returns the job title, not the salary.

To fix it:

• Count columns from the first column of your selected range
• Check that the column number matches the value you want to return
• Make sure the column exists inside the range

Approximate match issues

If you use TRUE, VLOOKUP() looks for the closest lower match instead of requiring an exact one.

If you type =VLOOKUP("E1011", A:G, 7, TRUE) and there is no E1011 in the dataset,  instead of returning an error, the formula returns 88000, which belongs to E1010.

That result may look correct, but it is not an exact match.

To fix it:

• Use FALSE when you need an exact result

• Use TRUE only when the data is sorted and an approximate result is acceptable

Range problems

VLOOKUP() may also stop working when the selected range does not match what the formula is trying to do.

For example, =VLOOKUP("E1005", A:C, 7, FALSE) returns #REF! because the range A:C has only 3 columns, but the formula asks for column 7.

Another common issue happens when the lookup column is not the first column in the range.

See this formula:

=VLOOKUP("E1005", B:G, 7, FALSE)

Here, the first column in the range is column B, not column A. Since VLOOKUP() only searches the first column of the selected range, it cannot find E1005.

To fix it:

• Make sure the lookup column is the first column in the range
• Make sure the range includes the column you want to return
• Double-check the selected range before finalizing the formula

Wrong range throws an error. Image by Author.

VLOOKUP() Across Sheets in Google Sheets

You can use VLOOKUP() to pull data from another sheet by adding the sheet name before the range.

The format looks like this:

=VLOOKUP(search_key, SheetName!range, index, FALSE)

The SheetName! part tells the formula which sheet to search in.

Suppose your data is stored in a sheet called Employees, and you are working in another sheet. To find the department for employee ID E1005, use:

=VLOOKUP("E1005", Employees!A:G, 4, FALSE)

Here:

• Employees!A:G looks in the Employees sheet

• 4 returns the Department column

The formula returns the department for E1005.

Reference another sheet in VLOOKUP(). Image by Author.

When sheet names have spaces

If the sheet name contains spaces, wrap it in single quotes like this: 

=VLOOKUP("E1005", 'Employee Data'!A:G, 7, FALSE)

Without the quotes, the formula will not work.

VLOOKUP() vs. INDEX MATCH in Google Sheets

VLOOKUP() searches in a fixed column and returns data based on position, while INDEX MATCH finds values using row and column references directly, so it works even if the table structure changes.

VLOOKUP: fast to write, easy to break

You already saw this =VLOOKUP("E1005", A:G, 7, FALSE) finds the salary for E1005. 

It works well when:

• The lookup column is on the left
• The table structure does not change

Problems start when the structure changes.

For example:

• You insert a new column
• The salary column shifts from 7 to 8
• The formula still uses 7

Now it returns the wrong value without showing an error.

INDEX MATCH: longer, but stable

Now see the same lookup using INDEX MATCH:

=INDEX(G:G, MATCH("E1005", A:A, 0))

Here:

• MATCH() finds the position of E1005 in column A

• INDEX() returns the value from column G at that position

This method does not depend on column numbers, so it keeps working even if the table changes.

INDEX MATCH handles the data better than VLOOKUP(). Image by Author.

Where it actually helps

Use INDEX MATCH when your data is not arranged left to right. Suppose Column A contains Employee ID and Column C contains Last Name. If you want to search by Last Name and return Employee ID, this would be a right-to-left lookup.

VLOOKUP() cannot do this without changing the table, but INDEX MATCH handles it directly:

=INDEX(A:A, MATCH("Wilson", C:C, 0))

No need to rearrange your data.

VLOOKUP() vs XLOOKUP in Google Sheets

VLOOKUP() searches within a fixed structure and has limitations, while XLOOKUP() allows lookups in any direction. It was added to Google Sheets in 2022 and is now the better option in many cases.

Let’s say you want to find the Employee ID using the Last Name Wilson. This is a right-to-left lookup. VLOOKUP() cannot handle this without rearranging the table, but XLOOKUP() can:

=XLOOKUP("Wilson", C:C, A:A)

This formula:

• Searches Last Name (column C)
• Returns Employee ID (column A)

XLOOKUP() in Google Sheets. Image by Author.

XLOOKUP() is better because it: 

• Works in any direction (left or right)
• Does not use column numbers
• Returns exact matches by default
• Allows custom output when no match is found 

Final Thoughts 

If I had to give one piece of advice: build VLOOKUP() into a real spreadsheet you actually care about instead of a practice sheet. The errors hit differently when the data is important. You'll remember why FALSE exists the first time TRUE silently gives you someone's wrong salary.

Once you're comfortable, try different match types, adjust your ranges, and notice when the results stop making sense. That's usually the point where a more flexible option like INDEX MATCH() or XLOOKUP() makes more sense.