How to Use VLOOKUP in Google Sheets

Online Tech Tips is reader-supported. We may earn a commission when you buy through links on our site. Learn more.

Life is messy, isn’t it? Things like tracking finances and managing time are messy and time-consuming. Yet, these are things that, if put in order, would improve your life. Spreadsheets can help every day with these sorts of tasks.

It can be challenging to find information in spreadsheets, however. That’s why we’re going to show you how to use the VLOOKUP function in Google Sheets to make finding something in a spreadsheet a lot easier.

Table of Contents
    How to Use VLOOKUP in Google Sheets image - Sankt-Petersburg, Russia, August 16, 2018: Google Sheets icon on Apple iPhone X smartphone screen close-up. Google sheets icon. Social network. Social media icon

    VLOOKUP is a Sheets function to find something in the first column of a spreadsheet. The V is for vertical because, like columns on a building, spreadsheet columns are vertical. So when VLOOKUP finds the key thing we’re looking for, it will tell us the value of a specific cell in that row.

    The VLOOKUP Function Explained

    In the image below is the VLOOKUP function’s syntax. This is how the function is laid out, regardless of where it’s being used.

    The VLOOKUP Function Explained image - 3-vlookup-function-syntax

    The function is the =VLOOKUP( ) part. Inside the function are:

    • Search Key – Tells VLOOKUP what it needs to find.
    • Range – Tells VLOOKUP where to look for it. VLOOKUP will always look in the leftmost column of the range.
    • Index – Tells VLOOKUP how many columns to the right of the left-most column in the range to look for a value if it finds a match of the search key. The left-most column is always 1, the next to its right is 2, and so on.
    • Is sorted? – Tells VLOOKUP if the first column is sorted. This defaults to TRUE, which means VLOOKUP will find the nearest match to the search key. This can lead to less accurate results. FALSE tells VLOOKUP that it must be an exact match, so use FALSE.
    The VLOOKUP Function Explained image 2 - 4-vlookup-function-syntax-with-values

    The VLOOKUP function above will use whatever value is in cell E1 as its search key. When it finds a match in column A of the range of cells from A1 to C5, it will look in the third column of the same row as it found the match and return whatever value is in it. The image below shows the results of entering 4 in cell E1. Next, let’s look at a couple of ways to use the VLOOKUP function in Google Sheets.

    The VLOOKUP Function Explained image 3 - 5-vlookup-function-result

    Example 1: Using VLOOKUP For Tracking Jobs

    Let’s say you have a service business and you want to find out when a work order starts. You could have a single worksheet, scroll down to the work order number and then look across the row to find out when it starts. That can become tedious and prone to error.

    Example 1: Using VLOOKUP For Tracking Jobs image - 6-vlookup-manual-lookup

    Or you could use VLOOKUP.

    1. Enter the headings Work Order and Work Date somewhere on the worksheet.
    Example 1: Using VLOOKUP For Tracking Jobs image 2 - 7-vlookup-trackjob-1
    1. Select the cell to the right of Work Date and start entering the formula =VLOOKUP. A help box will pop up as we type, showing us available Google Sheet functions that match what we’re typing. When it shows VLOOKUP, press Enter, and it will complete the typing.
    Example 1: Using VLOOKUP For Tracking Jobs image 3 - 8-vlookup-trackjob-2
    1. To set where VLOOKUP will find the Search Key, click on the cell right above this.
    Example 1: Using VLOOKUP For Tracking Jobs image 4 - 9-vlookup-trackjob-3
    1. To select the Range of data to search in, click and hold on the A column header and drag to select everything over to, including column H.
    Example 1: Using VLOOKUP For Tracking Jobs image 5 - 10-vlookup-trackjob-4
    1. To select the Index, or column, that we want to pull data from, count from A to H. H is the 7th column so enter 7 in the formula.
    Example 1: Using VLOOKUP For Tracking Jobs image 6 - 11-vlookup-trackjob-5
    1. Now we state how we want the first column of the range to be searched. We need an exact match so enter FALSE.
    Example 1: Using VLOOKUP For Tracking Jobs image 7 - 12-vlookup-trackjob-6a

    Notice that it wants to put an opening curved bracket after FALSE. Press backspace to remove that.

    Example 1: Using VLOOKUP For Tracking Jobs image 8 - 13-vlookup-trackjob-6b

    Then enter a curved closing bracket ), and press Enter to finish the formula.

    Example 1: Using VLOOKUP For Tracking Jobs image 9 - 14-vlookup-trackjob-6c

    We’ll see an error message. That’s ok; we did things correctly. The issue is that we don’t have a search key value yet.

    Example 1: Using VLOOKUP For Tracking Jobs image 10 - 15-vlookup-trackjob-6d

    To test the VLOOKUP formula, enter the first work order number in the cell above the formula and press Enter. The date returned matches the date in the WorkDate column for work order A00100.

    Example 1: Using VLOOKUP For Tracking Jobs image 11 - 16-vlookup-trackjob-6e

    To see how this makes life easier, enter a work order number that isn’t visible on the screen, like A00231.

    Example 1: Using VLOOKUP For Tracking Jobs image 12 - 17-vlookup-trackjob-6f

    Compare the date returned and the date in the row for A00231, and they should match. If they do, the formula is good.

    Example 2: Using VLOOKUP to Calculate Daily Calories

    The Work Order example is good but simple. Let’s see the actual power of VLOOKUP in Google Sheets by creating a daily calorie calculator. We’ll put the data in one worksheet and make the calorie calculator in another.

    1. Select all the data on the food and calorie list.
    Example 2: Using VLOOKUP to Calculate Daily Calories image - 18-vlookup-calorie-counter-1
    1. Select Data > Named Ranges.
    Example 2: Using VLOOKUP to Calculate Daily Calories image 2 - 19-vlookup-calorie-counter-2
    1. Name the range FoodRange. Named ranges are easier to remember than Sheet2!A1:B:29, which is the actual definition of the range.
    Example 2: Using VLOOKUP to Calculate Daily Calories image 3 - 20-vlookup-calorie-counter-3
    1. Go back to the worksheet where food is tracked. In the first cell in which we want calories to show, we could enter the formula =VLOOKUP(A3,FoodRange,2,False).
    Example 2: Using VLOOKUP to Calculate Daily Calories image 4 - 21-vlookup-calorie-counter-4a

    It would work, but because there’s nothing in A3, there will be an ugly #REF error. This calculator might have many Food cells left blank and we don’t want to see #REF all over it.

    Example 2: Using VLOOKUP to Calculate Daily Calories image 5 - 22-vlookup-calorie-counter-4b
    1. Let’s put the VLOOKUP formula inside an IFERROR function. IFERROR tells Sheets that if anything goes wrong with the formula, return a blank.
    Example 2: Using VLOOKUP to Calculate Daily Calories image 6 - 23-vlookup-calorie-counter-5
    1. To copy the formula down the column, select the handle at the bottom-right corner of the cell and drag it down over as many cells as needed.
    Example 2: Using VLOOKUP to Calculate Daily Calories image 7 - 24-vlookup-calorie-counter-6a

    If you think that the formula will use A3 as the key down the column, don’t worry. Sheets will adjust the formula to use the key in the row that the formula is in. For example, in the image below, you can see that the key changed to A4 when moved to the 4th row. Formulas will automatically change cell references like this when moved from column to column, too.

    Example 2: Using VLOOKUP to Calculate Daily Calories image 8 - 25-vlookup-calorie-counter-6b
    1. To add up all the calories in a day, use the =SUM function in the blank cell next to Total, and select all the rows of calories above it.
    Example 2: Using VLOOKUP to Calculate Daily Calories image 9 - 26-vlookup-calorie-counter-7a

    Now we can see how many calories we had today.

    Example 2: Using VLOOKUP to Calculate Daily Calories image 10 - 27-vlookup-calorie-counter-7b
    1. Select the column of calories from Monday and paste it to the Calories column for Tuesday, Wednesday, and so on. 
    Example 2: Using VLOOKUP to Calculate Daily Calories image 11 - 28-vlookup-calorie-counter-8a

    Do the same for the Total cell below Monday. So now we have a weekly calorie counter.

    Example 2: Using VLOOKUP to Calculate Daily Calories image 12 - 29-vlookup-calorie-counter-8b

    Summing Up VLOOKUP

    If this is your first dive into Google Sheets and functions, you can see how useful and powerful functions like VLOOKUP can be. Combining it with other functions like IFERROR, or so many others, will help you do whatever you need. If you enjoyed this, you might even consider converting from Excel to Google Sheets.

    Leave a Reply

    Your email address will not be published. Required fields are marked *