How to Leverage Google Sheets and VLOOKUP

Many people have asked for more tutorials using Google’s suite of products. One prominent question is “how does VLOOKUP work in Google Sheets?”. This function is hugely popular among Google and Microsoft spreadsheet enthusiasts. In this example, I’ll show how to get started. (Includes resource spreadsheet).

— If you’re an Excel user, please check out the Excel VLOOKUP tutorial.

What is a Lookup Function?

If we remove the concept of spreadsheets, you probably do a similar function when you go to a restaurant. You scan the menu for some tasty options and prices. And just as you come to the end, there’s a reference to “daily specials”. Now, you have to hunt for where those items are.

The same issue happens with data. Instead of menus, you might have multiple worksheets from different sources and the data isn’t always organized the way you want. In the picture below, can you guess what is a Pcode?

What’s a Pcode?

It’s a code that represents the voter’s political party. You wouldn’t guess that unless you saw a related spreadsheet with additional information. Below you can see the value in Pcode is the same as the value in PARTY CODE.

Political Party Codes & Names

What would make more sense is to add a column to the first worksheet that displays the political party name from the second worksheet. Using named ranges and the vlookup function, we can improve the table.

New column for Political Party

Part 1 – Visualizing the Lookup

When building the lookup, it helps to define what you want and where the values are located because of the column order.

In my case, I want to:

1. Add a column to my Voters tab called Political Party
2. Look at a voter’s Pcode and find its corresponding NAME on the Party Codes tab
3. Copy that NAME to the voter’s Political Party column
4. Repeat the process for each voter

Mapping the data

Stating it another way, for each voter, we’ll grab their “Pcode” and search for a matching value in the “PARTY CODE” column on the other worksheet. If we find a match, we’ll look in the “NAME” column and copy that value to the voter’s “Political Party”.

Before we begin, there are several important rules to know about the reference column you need to look up. In our case, this will be the PARTY CODE column on the Party Codes worksheet.

• The PARTY CODE column must be the left-most or first column.
• The PARTY CODE column values must be unique.
• The PARTY CODE column should be sorted.

For example, I shouldn’t have the PARTY CODE column with multiple instances of the letter “A”. And as you’ll see in the sample file, the values in this column are sorted starting with numbers and then letters.

Part 2 – Importing the Tutorial File

To make this tutorial easier, I’ve created a sample spreadsheet. You can right-click it and choose Save link as….

1. Open a new Google sheet at https://sheets.google.com.
2. Click Upload.
3. Upload the sample tutorial file referenced above.
4. Select the Replace spreadsheet radio button.
5. Click Import.

Part 3 – Creating a Named Range in Google Sheets

If you’re not familiar with a named range, it’s a way of defining a cell range using meaningful names instead of absolute cell references. This becomes very handy when you reference ranges in formulas.

Instead of having a reference like 'Party Codes'!A2:B45 we can use a named range called PartyTable.

1. On the example spreadsheet, click the Party Codes. tab
2. Click cell A1.
3. Press Ctrl + A or command ⌘ + A to highlight the range.
4. Right-click and select Define named range…. You may have to scroll a bit to see this option.


Defining the Named Range

5. The Named ranges dialog will appear on the right. Change the name to PartyTable and click Done.

Part 4 – Building the Formula

The VLOOKUP function has 4 arguments. The last item, Sort_order is optional but critical. This TRUE/FALSE value specifies our matching precision. By default, Google will use a TRUE value which means it will match on a close value. That’s probably not a good idea when it comes to politics.

1. On the Voter’s tab, add your Political Party column in column D.
2. Click cell D2.
3. Start typing =vlookup. You may prefer to use the formula bar above the column headings as it has more visible room.

You can click the small blue question mark for online help.



Typing the VLOOKUP formula

4. After the opening parenthesis, we’ll click cell C2. This is our “Search_Criterion”.
5. Type a comma to separate our arguments.
6. Type PartyTable. This Named Range is our “Array”.
7. Type another comma.
8. Type 2. This is our “Index”. We’re telling Google to search for the values in the 2nd column on our Array.
9. Type another comma.
10. Type FALSE. This is our “Sort_order”. The FALSE means we want an exact match.
11. Type closing parenthesis.


VLOOKUP Function

In the example above, Google Sheets shows the lookup value of “Democratic” in the formula bar. This indicates the formula found a lookup value.

12. Click Enter to commit the function.
13. Click and drag the sizing handle (blue square) on D2 down the column to copy the formula to the other cells.

Copying VLOOKUP to other cells