If you Use VLOOKUP in Google Sheets You Need This

Do you have a need to find matches in Google Sheets?  Come on a journey with us through 7 questions to help you understand the VLOOKUP function in Google sheets and apply it for your use. But, we also like to hike, So, while we work through VLOOKUP and help you discover practical application, we’ll break things up with hikes through New River Gorge National Park.  Stay until the end where we’ll share with you the most underrated use of VLOOKUP.  Let’s get started.  To watch the video version of this post click here or continue reading below.

What is a VLOOKUP?

In Google Sheets, VLOOKUP (also known as Vertical Lookup) is a function used to search for a value in the first column of a range or table and return a corresponding value from another column in the same row. It's particularly useful for matching data across tables or finding specific information in large datasets.  In this example we are going to use Vlookup to fill in a part number and return metadata for that part.  Another way you could use VLOOKUP is to help with an address book where you can fill in a person’s name and return their address, phone number, or birthday.  VLOOKUP is extremely useful to small businesses as you will discover next. 

Right now, If you would like to follow along, click here and get the free practice worksheet right now. I'll wait…..Now that you have a copy of your own Let’s get into the details with this workbook and the next question

How do I use VLOOKUP in Google Sheets?

On the first sheet titled Inventory we have a list of inventory with Part, Description, Color, Quantity, Cost, and Total Value.  Here I want to use VLOOKUP to key in the part number and return the quantity in inventory.  So, I’m going to type in the word “Hoodie” in cell A5 because I want to return the number of hoodies in stock for quick reference.  You can see where this would be helpful when there are 100s of inventory items in a list.  In Cell B2 I am going to create the VLOOKUP formula to return the correct quantity.  Are you ready with your copy of this workbook?  Type in =vlookup( and you will see a formula popup.  Here you will notice the elements of the formula include search key, range, index, and is sorted.  The search key is the item you want to find.  In our case it’s the hoodie.  The range is the table we want to search or the inventory list.  The index is the column you want to return.  So, in our data we have columns D-I.  D is column 1, E is column 2, then 3,4,5, and 6 is column I.  So, we want our index to be 4 to pull the quantity.  The final element “is sorted” basically is asking whether the first column in your range is sorted or not.  Generally this will be False, but sometimes you can choose true.  If you click on the box you can see more information from google regarding this formula.  But, let’s finish the formula and see how it works.  Starting with =vlookup( type in a5,d5:i12,4,false) and let’s see what we get.  Here you can discover that the formula returned 5 hoodies in inventory.

Before we discuss how to do a vlookup between two worksheets in Google Sheets let’s review…

What are the 3 rules for VLOOKUP?

There are 3 required arguments for VLOOKUP and one optional.  The search key is the value to search for, in our case the part Hoodie.  The Range are the cells to consider for the search.  The first column in the range is the column that is searched on for the specific search key.  The index is the column number, how many columns from the left, of the column you want to return the result of when the search key is found.  Yeah, a little complicated there but basically, you just want to choose the column where the result is going to come from.  And the optional rule is the is sorted where most of the time this is False so the formula will return only an exact match.  

We’re going to go deep into VLOOKUP but first are you liking this training format? 

We focus on Google Sheets in this way to help you take your Google Sheets to the next level.  Join our blog above for even more Google Sheets Education. Ok, let’s dive into the next question and 

How do you use VLOOKUP Between Two Sheets?

Many times the VLOOKUP will be used to capture data from one worksheet and pull it onto another worksheet.  In our practice workbook let’s move to the Customer sheet and you will discover a list of names with job title, address, city, state, zip code, phone number, birthday and company name.  This is typical of what you would see in client or contact list for a small business.  But what if we wanted a quick way to return the birthday of a contact so that we build better relationships more efficiently.  With this data and VLOOKUP we can do that.  Let’s move to the Birthday sheet and here you will see three names from the previous sheet.  The easiest way to link two sheets is to let google sheets fill in some of the formula for you. 

So, in cell b5 type in =vl and the function list will automatically go to vlookup press the tab key to accept, then use your mouse to click on cell a5 Derrick Barnes to select the search_key type , now we need to select our range, click on the Customer sheet, highlight with your mouse cells a6:i12, type , then count the number of columns from Name to Birthday or 8 and type in 8 to select the index column then type ,false) enter to complete the formula.  The date of 10/5/1983 is returned which is Derrick Barnes birthday.  Now we want to do the same for Matt and Ms. Davidson.  So, copy and paste the formula to b6 and b7.  Oops we got an #n/a for Mr Simpson and Ms Davidson, why?

Why do You Get an #n/a on a VLOOKUP Formula?

There are a couple of reasons you may get an #n/a returned with the VLOOKUP formula.  Let’s look at the first example and select cell B6.  Here when we hover on the comment it says that it didn’t find Matt Simpson.  So, let’s look at the customer sheet and there he is on row 6.  Let’s go back to our formula and take a look.  Here you will see the range starts on row 7 and ends on 13.  Let’s look at the formula above.  Ahh, the range above is correct starting on row 6 and ending on row 12.  To fix this we need to add strings or dollar signs to the range to activate absolute cell referencing in the formula.  So in cell b5 add strings in front of the a and the 6 and the i and 12.  And now copy that formula to b6 and b7.  So, that fixed Mr. Simpson but Ms. Davidson is still not correct.  Why?  Let’s review the formula.  The formula is correct so what is the issue?  Let’s look at the Customer sheet again.  Her name is Tierna Davidson.  Now let’s look at the Birthday sheet.  Aaah here is the issue Tierna is misspelled without the i.  Let’s correct her name and see what happens.  Add the i to cell a7 and her birthday of 4/5/1970 is the correct result.

Where do you see value in VLOOKUP?  Join the conversation and comment.  We’d like to hear your thoughts.  

We’ve discussed VLOOKUP in detail.  But this post needs to include the question

How do you Find Approximate Matches in Google Sheets?

I think the most underrated use of VLOOKUP is its use in identifying approximate matches.  What if you had a list of inventory like on our cost category sheet and you wanted to classify each product into low cost, medium cost and high cost?  Well you can use VLOOKUP to programmatically fill this in for you.  In cell g5 type in =VLOOKUP(e5,i5:j7 and add the strings for absolute cell referencing so when you copy the formulas this table remains the same ,2)  Here you will see that the formula return “Low” because the $5 cost is greater than 0 but less than $7.  Let’s copy and paste this down to see how this works with more data.  I’m going to hit CTRL+C on cell G5 and then paste special formulas on the range g6:g12.  Now you can see that I’ve added the cost category to every part in the inventory list.  As the business evolves, I can change the price of the categories to adjust the price of low, medium, and high.  For example, if I change Medium and High to $10 and $20 we get different results. Notice that the Tshirt at $10 is Medium cost and the Hoodie at $20 is High cost.  So, $10-$19.99 will return Medium in this example.  But,

How do you Find an Exact Match in Google Sheets?

This is a good time to discuss exact match.  So, in approximate match above I use only 3 elements in the VLOOKUP formula.  But, if I add false to the fourth element by typing in G6 =vlookup(E6,$I$5:$J$7,2,false) it will return Medium because there is a $10 in the index of our range.  But, if I adjust cell G5 in the same manner and type in =vlookup(E5,$I$5:$J$7,2, false) it will return the #n/a error.  This is because the range does not have a $5 value only $0, $10, and $20.  So that is how you return an exact match vs an approximate match in Google Sheets.

To learn the SUM function and use it next level read this next