Using VLOOKUP in Google Sheets

It’s time to explore the wonderful world of VLOOKUP! It’s like a search party for data inside of Google Sheets, helping you to find what you’re looking for without wading through an endless stream of information. This function helps you search for information across spreadsheet columns like seasoned detectives (or a lost sock in the laundry). To start it up, we type =VLOOKUP in a cell, and the journey begins!

VLOOKUP allows us to pull data from the depths of our spreadsheets, and it requires some key components:

Understanding the Syntax of VLOOKUP

The syntax for VLOOKUP might seem like a bit of a tongue twister but don’t worry, we’re all friends! Here’s how it looks:

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

Breaking it down further:

• search_key: This is the value we want to search for in the first column of the range. For example, a product ID like “P123.”
• range: This range contains the data we want to search.
• index: This indicates which column’s value we want to return. For instance, if we want to retrieve the price from the third column in the range, we set this to 3.
• [is_sorted]: This is an option input. Choose from:
  • FALSE = An exact match. This is recommended in most cases.
  • TRUE = Approximate match. This is the default if you don’t specify the is_sorted value.

Important: Before using an approximate match, make sure your search key column is sorted in ascending order, otherwise you may return the wrong value.

Now, once we input this into Google Sheets, it searches for the specified lookup value in the first column of our defined range and pulls the data from the column we want. No more hunting through your spreadsheet like it’s a treasure map!

In the example below, I’ve used VLOOKUP to search for the price of a part in my list. Since each part has a unique identifier, Part ID, I can use this as my search key. Then I told the VLOOKUP to locate the corresponding price in column 4. Finally, I used the FALSE keyword to make sure that it returned an exact match.

VLOOKUP quickly found the value I was looking for.

Breaking it down, you can see my formula tells the VLOOKUP to use cell G3 as the search key. Referencing a cell in this way makes it easy for me to type a different value into the cell when I want to look for a different item.

I then identified the range (A4:D8) so that the formula knows exactly where the data is located. Then I just identified the column containing the price.

There are a couple of things to note:

• VLOOKUP always uses the first column in the range to find the search key. In my example, this is the Part ID. If your search key column is somewhere other than the first column in the range, you’ll need to move your columns around before using VLOOKUP.
• Although the columns in a spreadsheet are labelled A, B, C etc, you need to refer to them numerically in your formula for the VLOOKUP to work. I added numbers above my data range for the purpose of this example, but the numbers are only actually needed inside the formula itself.

VLOOKUP Error Messages

Sometimes, when you use a VLOOKUP function, you’ll find yourself confronted with the dreaded #N/A error message. Don’t panic, all it means is that the search key could not be found in the range. This generally happens for one of two reasons. First, you may have made a typo. Shocking, I know, but there it is!

The first thing to do is check your typing. If you made a boo boo, just fix it and move on. On the other hand, if you know you typed everything correctly (and the rest of your formula is set up properly) and it still comes up with an error, it means that the item you’re looking for is not in the list.

Sometimes, the error tells you exactly what you need to know, i.e. you don’t have the part in stock, so it’s time to reorder more widgets!

Conclusion

Now that you’ve dipped your toes into the sparkling waters of VLOOKUP, you might be feeling ready to take your skills on the road to impress your team and show them why you should use VLOOKUP in Google Sheets!