VLOOKUP Google Sheets Explained: What It Does and How to Use It

If you’ve ever had to deal with massive spreadsheets like hundreds or even thousands of rows you know how painful it is to manually match things up. Back when I was working in a company doing daily reports, we had these giant Google Sheets filled with product codes, sales logs, and client details. I’d waste so much time scrolling and Ctrl+F-ing just to find the right data to match. Total nightmare.

That’s when I discovered VLOOKUP Google Sheets. It felt like a cheat code suddenly I could just plug in a formula, and boom, the info I needed would auto-fill. Whether you're working in marketing, sales, logistics, or any kind of admin task, this function is a life-saver for matching and pulling data fast without messing around.

In this guide, I’ll walk you through how to use VLOOKUP in Google Sheets, how to write the formula correctly, and how you can use it to simplify your workflow. By the end, you’ll get the hang of using VLOOKUP Google Sheets style quick, clean, and way less frustrating.

What Is VLOOKUP?

VLOOKUP stands for “Vertical Lookup”, and it does exactly what it sounds like it searches vertically down a column to find a match and then returns something from that same row.

Think of it like this: you’ve got two sets of data. One has a list of product IDs and their names, and another sheet just has the product IDs. You want to pull the product names into the second sheet. Instead of doing it manually, you use VLOOKUP to search for the product ID in the first list and bring back the matching name automatically.

It’s one of those formulas that sounds a bit technical at first, but once you use it once or twice, you’ll get the hang of it quickly and it becomes super handy when working with price lists, inventory, customer info, or anything where you’re matching values from one list to another.

How VLOOKUP Formula Works in Google Sheets

Before we dive into an example, let’s quickly break down what this formula does:

A2 is the value you want to search for in this case, the product name you’ll type.
$A$6:$B$15 is the range where your data lives the product names and prices.
2 means return the value from the second column of that range (the price column).
FALSE tells Google Sheets to look for an exact match.

This formula searches for the product name you enter in A2 by looking down the first column of the specified range (A6:A15). When it finds a match, it returns the value from the second column of that range (B6:B15), which is the price.

Keep in mind, your setup might be different. For example:

If you type the product name in a different cell, like D3, replace A2 in the formula with D3.
If your product names and prices are in a different part of the sheet, update the range $A$6:$B$15 to match your actual data range.
The number 2 tells the formula to pull data from the second column in the range; if your price is in the third column, change this number to 3.

Basically, you need to adjust the formula references to fit your sheet’s layout so it can find the data correctly.

Using VLOOKUP in Google Sheets (Step-by-Step)

Now that you know how the formula works, let’s put it into action with a sample table.

Step 1: Organize your data

For this sample, we’ll use a table where product names are in column A (A6:A15) and their prices are in column B (B6:B15).

Product NamePriceSmart Watch149.99Smartphone699.00LED TV399.99Headphones89.99Bluetooth Speaker55.00Laptop999.99Tablet299.50Wireless Mouse25.99Gaming Console399.00Fitness Tracker120.00

Step 2: Input product name and apply formula

In A2, type the product name you want to find the price for.
In B2, enter the formula this means typing or pasting it directly into cell B2, where the price (or the matching value) will show up.

Step 3: Check the price

Once you type the product name in A2, the price will appear automatically in B2.

Screenshot example

Here’s a screenshot to show how this looks in practice you’ll see the data table, the product name input in A2, and the price appearing in B2 with the formula visible.

vlookup formula screenshot sample of use

Handling “Not Found” Results in VLOOKUP

If you type a product name in A2 that doesn’t exist in your data table, the formula will return an error like #N/A, which means no exact match was found. To avoid showing this error and display a friendly message instead, you can wrap your VLOOKUP formula inside an IFERROR function like this:

This way, if the product isn’t found, Google Sheets will show “Product not found” instead of an error. It keeps your sheet looking clean and easier to understand.

6. Recap: Adjusting the VLOOKUP Formula for Your Data

By now, you’ve seen how VLOOKUP works using a basic example, but your actual sheet might not be set up the same way and that’s totally fine. Here’s a quick recap on what you can tweak:

Cell to search for: If your search term (like a product name or ID) is in a different cell, change the first part of the formula. So instead of A2, it might be D3, F5, etc.
Data range: This is the chunk of your sheet where the formula will look for matches. If your list starts in row 2 instead of row 6, just update the range part like this: A2:B10, or whatever matches your actual data.
Column number: This tells Google Sheets which column to pull the value from. In our example, it’s 2 because prices are in the second column. But if your price is in column C, and your range is A6:C15, then change the number to 3.
Match type: Always use FALSE to get an exact match unless you have a reason to allow “close enough” results (which is rarely the case for product data).

Think of the VLOOKUP formula like a flexible tool you just need to point it at the right spot, and it’ll do the rest.

Final Thoughts

VLOOKUP is one of those Google Sheets tricks that saves time and headaches once you get the hang of it, you’ll wonder how you ever managed without it. Whether you’re looking up prices, pulling product details, or matching IDs, this formula is a real lifesaver.

If you work with Sheets a lot, you might also want to check out this guide on how to highlight duplicates in Google Sheets. It’s a quick way to clean up messy data before running formulas like VLOOKUP.