VLOOKUP Function in Google Sheets

If you want to look up a value in one table and pull back a matching value from another column, the VLOOKUP function is what you reach for. You hand it a lookup value, point it at a table, and tell it which column to return.

In this article, I’ll walk you through how VLOOKUP works in Google Sheets, with five examples that go from a plain lookup all the way to handling missing values and picking the return column on the fly.

VLOOKUP Function Syntax in Google Sheets

Here is how the function is put together.

=VLOOKUP(search_key, range, index, [is_sorted])
  • search_key – the value you want to find. This is matched against the first column of the range.
  • range – the table to search in. The first column is where the lookup happens.
  • index – the column number (counting from the left of the range) whose value you want back.
  • is_sorted – FALSE for an exact match, TRUE for an approximate match on sorted data. Optional, but you’ll almost always want FALSE.

When to Use VLOOKUP Function

  • Pulling a price, salary, or status from a reference table using an ID or name.
  • Matching records between two lists, like getting an email from an employee number.
  • Slotting an approximate value into a band, such as a grade from a score range.
  • Building a quick lookup field next to a column of IDs that someone typed in.
  • Combining VLOOKUP with other functions to pick the return column based on a header.

Example 1: Look Up an Exact Salary by Name

Let’s start with the most common use of VLOOKUP, finding an exact match.

Below is the dataset. Column A has employee names and column B has their salaries. Column D has a few names we want to look up.

Google Sheet with employee names and salaries, plus a lookup list for VLOOKUP.

We want to pull each person’s salary from the table into column E.

Here is the formula:

=VLOOKUP(D2,$A$2:$B$6,2,FALSE)
VLOOKUP(D2, $A$2:$B$6, 2, FALSE) in Google Sheets cell E2 returns Carlos's salary.

VLOOKUP takes the name in D2, finds it in the first column of $A$2:$B$6, and returns the value from column 2 of that range, which is the salary. The last argument FALSE forces an exact match.

The range uses dollar signs so it stays locked when you fill the formula down. The first result comes back as 71000.

Pro Tip: Keep the lookup column (column A here) as the leftmost column of your range. VLOOKUP can only search the first column of the range you give it.

Example 2: Approximate Match for Grade Bands

Sometimes you don’t want an exact match. You want the closest value at or below your lookup, which is what the approximate match is for.

Below is the dataset. Column A holds score thresholds in ascending order, column B holds the grade letter for each band, and column D has the scores you want to grade.

Google Sheets with grade lookup data (Min Score, Grade) and student scores awaiting VLOOKUP.

We want to assign a grade to each score in column D.

Here is the formula:

=VLOOKUP(D2,$A$2:$B$6,2,TRUE)
Google Sheets: VLOOKUP(D2, $A$2:$B$6, 2, TRUE) in E2 shows approximate match grading.

With TRUE as the last argument, VLOOKUP doesn’t need an exact hit. It walks down the first column and stops at the largest threshold that is still less than or equal to the score, then returns the grade next to it.

The first score returns a grade of C. This only works when the first column is sorted in ascending order. If it isn’t, the result is unreliable.

Pro Tip: Approximate match is the one place a sorted lookup column is mandatory. For everything else, use FALSE and don’t worry about sort order.

Example 3: Return a Column Further Right

VLOOKUP isn’t limited to the column right next to your key. You can return any column in the range by its position number.

Below is the dataset. Columns A through D hold a product ID, name, price, and stock count. Column F has the product IDs we want to look up.

Google Sheet showing Product IDs, product details, lookup IDs, and an empty Stock column.

We want to pull the stock count, which sits in the fourth column of the table.

Here is the formula:

=VLOOKUP(F2,$A$2:$D$5,4,FALSE)
Google Sheets displays VLOOKUP(F2, $A$2:$D$5, 4, FALSE) in G2, returning stock 58.

The index here is 4, so VLOOKUP returns the value from the fourth column of the range, counting from column A as 1. The first lookup returns a stock count of 58.

One row returns 0, which is a real value, not an error. That product is genuinely out of stock, and VLOOKUP reports it as such.

Example 4: Handle Missing Values with IFERROR

When VLOOKUP can’t find the lookup value, it returns an #N/A error. That’s ugly in a report, so let’s catch it.

Below is the dataset. Column A has region codes, column B has the region names, and column D has codes to look up, including a couple that don’t exist in the table.

Google Sheets showing City/Region data (Austin, Boston) and Lookup Cities (Denver, Austin) for VLOOKUP.

We want the region name where the code exists, and a friendly message where it doesn’t.

Here is the formula:

=IFERROR(VLOOKUP(D2,$A$2:$B$5,2,FALSE),"Not found")
Google Sheets formula bar shows IFERROR VLOOKUP for missing city, E2 says "Not found".

IFERROR wraps the VLOOKUP. If the lookup succeeds, you get the region name like South. If the code isn’t in the table, instead of #N/A you get the text “Not found”.

This keeps your output clean and tells the reader the code is genuinely missing rather than the formula being broken.

Pro Tip: You can put anything in the IFERROR fallback, including an empty string “” to leave the cell blank, or a 0 if you’re feeding the result into a calculation.

Example 5: Pick the Return Column with MATCH

Here’s a trick that makes VLOOKUP much more flexible. Instead of hard-coding the column number, you let MATCH figure it out from a header.

Below is the dataset. Columns A through D hold a student name, math, science, and english scores. Column F has names to look up, and column G has the subject you want, typed as a header.

Google Sheet with student names, scores, and VLOOKUP lookup/subject columns.

We want the score for the named student in whichever subject column G asks for.

Here is the formula:

=VLOOKUP(F2,$A$2:$D$5,MATCH(G2,$A$1:$D$1,0),FALSE)
VLOOKUP formula in H2 uses MATCH to dynamically return student scores.

MATCH looks up the subject in G2 against the header row $A$1:$D$1 and returns its position. That number becomes the index argument for VLOOKUP, so the return column changes whenever the header in G2 changes.

The first row returns 95. Change G2 from one subject to another and the same formula pulls from a different column with no edits.

Pro Tip: This MATCH trick works horizontally too. If your data runs across rows instead of down columns, the HLOOKUP function is the sideways version of VLOOKUP.

Tips & Common Mistakes

  • The lookup column has to be first. VLOOKUP always searches the leftmost column of the range. If the value you’re matching on sits in the middle of your table, VLOOKUP can’t see it, and you’ll need to rearrange columns or reach for a different approach.
  • Forgetting FALSE bites people. Leave off the last argument and VLOOKUP defaults to approximate match. On unsorted data that quietly returns the wrong value with no error. Add FALSE unless you specifically want a banded lookup.
  • The index is range-relative, not sheet-relative. Column 2 means the second column of your range, not column B of the sheet. If your range starts at column C, then index 2 is column D. The CHOOSE function can help when you want to reorder columns before the lookup.

VLOOKUP covers a huge share of everyday lookup work in Google Sheets, from a plain name-to-salary match to picking the return column on the fly with MATCH.

Once you’re comfortable with exact match, approximate match, and wrapping it in IFERROR, you can handle most lookup tasks that come up.

List of All Google Sheets Functions

Related Google Sheets Functions / Articles: