One of the most popular and powerful options in Google Sheets is the vlookup function. This powerful yet simple function allows you to do so much when using a spreadsheet.
Learning how to do a vlookup may appear difficult at first. However, once you have got used to how it works you will have no problem adding the vlookup function to your Google Sheets spreadsheet.
WHAT IS A VLOOKUP
A vlookup is a function that you will find in all spreadsheet programs. The vlookup function in Google Sheets works the same as it would in Microsoft Excel.
The purpose of a vlookup is to use specific criteria, which is stored in a cell to find other data within the spreadsheet that is linked to that criteria. It’s a really quick way of getting information and it can be used for many different purposes.
This is one of the reasons why the vlookup is one of the most popular and loved functions in spreadsheets.
WHY A VLOOKUP IS USEFUL
A vlookup is useful when you have large amounts of data that you want to extract information from. Generally speaking you would have the raw data in a separate sheet. You would then add an extra sheet where you would use the vlookup function to extract the data from the sheet containing the raw data.
Let’s take a look at how to do a basic vlookup in Google Sheets. In this example you own a business selling sports equipment. You want to setup a spreadsheet to record sales. To avoid having to enter all the information about the product every time you sell an item. You have decided to do a vlookup.
The vlookup will allow you to record sales by entering just one piece of information. In this example that is the ‘product code’ of the item, you have sold.
HOW TO DO A VLOOKUP IN GOOGLE SHEETS
When using a vlookup you will usually have a large amount of raw data that you want to extract data from. It’s always best to separate the raw data by adding an extra tab to your spreadsheet. Below you can see I have two tabs. One is called ‘RAW_DATA’ and the other ‘vlookup’.
I have entered some basic raw data into the RAW_DATA tab. In this example, the raw data is the stock you have in your warehouse that is for sale. The information provides basic information about the product, which includes the product name, price, colour and stock levels.
I have then added the field names for the information I want to record when a sale takes place. This information has been added to the vlookup tab, as you can see below.
If you were not using a vlookup you would need to manually enter the information every time you sold an item. By doing a vlookup you will only need to enter the product code.
ENTERING THE VLOOKUP FORMULA IN GOOGLE SHEETS
We need to enter a vlookup formula in columns B to G. Column A is where you would manually type the product code.
Time needed: 6 minutes.
Entering the vlookup formula in cell B2
- Select cell B2
click on cell B2 with your mouse.
- Start the vlookup formula
To start a formula enter the ‘=’ sign followed by vlookup without any spaces. =vlookup
- Select the cell containing the product code
Continuing with your formula enter ( and click on cell A2 with your mouse.
- Select the raw data
Select the ‘Raw_Data’ tab with your mouse. Then highlight the range of data needed for the vlookup. Do this by holding down your left mouse button on cell A2 and dragging your mouse to E14. Then release the mouse
- Select the column you want to lookup from the raw data
You now need to choose the column that contains the correct result. Do this by entering a comma and typing the column number, which is column number 2.
- Close the vlookup formula
Close the vlookup by entering a comma and the word false. Then close with a bracket and press return.
You should now see the following in cell B2
You can see from above that the information is not yet showing. The reason for this is because we have not yet entered any information in cell A2. The formula needs information in cell A2 to find matching information in the Raw Data sheet.
Enter a product code in cell A2
Enter the product code BD401 in cell A2. Providing you have done the formula correctly you should now see something similar to what is showing below.
As you can see by entering the product code in cell A2. It has found matching information that matches the criteria of your vlookup formula.
UNDERSTANDING THE VLOOKUP FORMULA
So we have just entered the first vlookup needed for our sales spreadsheet. However, you may have questions about how the formula works. Before we go onto entering the remaining formulas let’s take a look at each aspect of the vlookup, so you can get better understanding.
This is what you enter to start the vlookup. Whenever you do a formula in Google Sheets you start with the ‘=’ sign. You then need to tell Google Sheets what function to use, which you do by entering ‘vlookup’.
To start selecting the criteria you open the formula with the open bracket ‘(‘.
You then need to select the column that contains data that matches data in your raw data. We have chosen the product code, which is in column A of your vlookup tab.
The next section of the vlookup is where you select the raw data. In our example we did this by clicking the ‘RAW_DATA’ tab and dragging the mouse over the cells containing the data.
As you can see from above when you do this it adds ‘RAW_DATA!A2:E14 to the formula. This part of the formula is confirming the range of cells where the raw data is stored. It’s also confirming this raw data is in the tab named ‘RAW_DATA’.
When you become more familiar with vlookups in Google Sheets. You can just enter the formula by typing it in whole; rather than using your mouse to select the different sections. Either way works and it all depends on the vlookup and raw data you are working with.
The next part of the formula is important. In our example we entered the number ‘2’. By entering the number 2 we are asking Google Sheets to bring back the data in column 2 of the raw data tab. In this example that is the column containing the item name.
We are entering number 2 because if you look at where our vlookup started. We are entering the vlookup in cell B2 of the vlookup tab. This column is headed ‘Item Name’, so we have asked the vlookup to match the product code in cell A2 of the vlookup tab with the information held in column 2 of the raw data sheet. It will only bring back the data if the product code in A2 of the Raw Data matches the product code of cell A2 in the vlookup tab.
If this is not making much sense, you will understand better when we do the remaining formulas.
To close the vlookup we entered ,false). This is simply telling the vlookup if the information is not matched then bring back a false result.
ADDING THE REMAINING VLOOKUPS
So we have entered the vlookup for column B. We now need to enter the remaining vlookup formulas for columns C to E.
The principle is exactly the same. There is only one change we need to make to the vlookup in the remaining columns. To help you understand what the change is doing, simply follow the steps below.
Type the following formula into cell C2 of the vlookup tab:
You may have noticed the only part of the vlookup formula we have changed is the number at the end of the formula. We have changed the number from 2 to 3. This is requesting the vlookup to bring back information in column C from the raw data tab.
You should have noticed that the information that the formula has provided is for the colour of the product.
You can now do the remaining vlookups. Using exactly the same vlookup formula. The only part you need to change is the number at the end of the formula.
Once this is done you should see something similar to what is showing below:
You can see the vlookup formulas are now bringing back the data for each column of your sales spreadsheet. You can check if it works for the remaining codes by simply dragging down the formulas in cells B2 to E2 to row 14. Then copy the codes in column A of the raw data sheet and paste these codes into column A of the vlookup tab.
You should then see something similar to below: