#StandWithUkraine - Stop the Russian invasion

Join us and donate. We are contributing all book royalties from 2022 to present to:
Save Life in Ukraine and Ukraine Humanitarian Appeal.

Match Columns with VLOOKUP

Spreadsheet tools also allow you to “look up” data in one sheet and automatically find and paste matching data from another sheet. This section introduces the VLOOKUP function, where the “V” stands for “vertical,” meaning matches across columns, which is the most common way to look up data. You’ll learn how to write a function in one sheet that looks for matching cells in select columns in a second sheet, and pastes the relevant data into a new column in the first sheet. If you’ve ever faced the tedious task of manually looking up and matching data between two different spreadsheets, this automated method will save you lots of time.

Here’s a scenario that illustrates why and how to use the VLOOKUP function. Figure 2.35 shows two different sheets with sample data about food banks that help feed hungry people in different parts of the US, drawn from Feeding America: Find Your Local Food Bank. The first sheet lists individual people at each food bank, the second sheet lists the address for each food bank, and the two share a common column named organization. Your goal is to produce one sheet that serves as a mailing list, where each row contains one individual’s name, organization, and full mailing address. Since we’re using a small data sample to simplify this tutorial, it may be tempting to manually copy and paste in the data. But imagine an actual case that includes over 200 US food banks and many more individuals, where using an automated method to match and paste data is essential.

Your goal is to create one mailing list that matches individual names and organizations on the left sheet with their addresses on the right sheet.

Figure 2.35: Your goal is to create one mailing list that matches individual names and organizations on the left sheet with their addresses on the right sheet.

  1. Open this Google Sheet of Food Bank sample names and addresses in a new browser tab. Log into your Google Drive, and go to File > Make a Copy to create your own version that you can edit.

We simplified this two-sheet problem by placing both tables in the same Google Sheet. Click on the first tab, called names, and the second tab, called addresses. In the future, if you need to move two separate Google Sheets into the same file, go to the tab of one sheet, right-click the tab to Copy to > Existing spreadsheet, and select the name of the other sheet.

  1. In your editable copy of the Google Sheet, the names tab will be our destination for the mailing list we will create. Go to the addresses sheet, copy the column headers for street - city - state - zip, and paste them into cells C1 through F1 on the names sheet, as shown in Figure 2.36. This creates new column headers where our lookup results will be automatically pasted.
Paste the last four column headers from the addresses sheet into the names sheet.

Figure 2.36: Paste the last four column headers from the addresses sheet into the names sheet.

  1. In the names sheet, click in cell C2 and type =VLOOKUP, and Google Sheets will suggest that you complete the full formula in this format:
VLOOKUP(search_key, range, index, [is_sorted])

Here’s what each part means:

  • search_key = The cell in 1st sheet you wish to match.
  • range = At least two columns in the 2nd sheet to search for your match and desired result.
  • index = The column in the 2nd sheet range that contains your desired result, where 1 = first column, 2 = second column, etc.
  • [is_sorted] = Enter false to find exact matches only, which makes sense in this case. Otherwise, enter true if the first column of the 2nd sheet range is sorted and you will accept the closest match, even if not an exact one.
  1. One option is to directly type this formula into cell C2, using comma separators: =VLOOKUP(B2,'addresses'!A:E,2,false). Another option is to click on the VLOOKUP Vertical lookup grey box that Google Sheets suggests, and click on the relevant cells, columns, and sheets for the formula to be automatically entered for you, as shown in Figure 2.37. What’s new here is that this formula in the names sheet refers to a range of columns A to E in the addresses sheet. Press Return or Enter on your keyboard.
The VLOOKUP formula in cell C2 of the names sheet (top) searches for matches across columns A to E in the addresses sheet (bottom).

Figure 2.37: The VLOOKUP formula in cell C2 of the names sheet (top) searches for matches across columns A to E in the addresses sheet (bottom).

Let’s break down each part of the formula you entered in cell C2 of the names sheet:

  • B2 = The search_key: the cell in the organization column you wish to match in the names sheet
  • 'addresses'!A:E = The range where you are searching for your match and results across columns A to E in the addresses sheet.
  • 2 = The index, meaning your desired result appears in the 2nd column (street) of the range above.
  • false = Find exact matches only.
  1. After you enter the full VLOOKUP formula, it will display the exact match for the first organization, the Central Texas Food Bank, whose address is 6500 Metropolis Dr. Click and hold down on the blue dot in the bottom-right corner of cell C2, and drag your crosshair cursor across columns D to F and let go, which will automatically paste and update the formula for the city, state, and zip columns, as shown in Figure 2.38.
Click on cell C2, then hold-and-drag the bottom-right blue dot across columns D to F, which automatically pastes and updates the formula.

Figure 2.38: Click on cell C2, then hold-and-drag the bottom-right blue dot across columns D to F, which automatically pastes and updates the formula.

  1. Finally, use the same hold-and-drag method to paste and update the formula downward to fill in all rows, as shown in Figure 2.39.
Click on cell F2, then hold-and-drag the bottom-right blue dot down to row 11, which automatically pastes and updates the formula.

Figure 2.39: Click on cell F2, then hold-and-drag the bottom-right blue dot down to row 11, which automatically pastes and updates the formula.

Warning: If you save this spreadsheet in CSV format, your calculated results will appear in the CSV sheet, but any formulas you created to produce those results will disappear. Always keep track of your original spreadsheet to remind yourself how you constructed formulas.

You’ve successfully created a mailing list—including each person’s name, organization, and full mailing address—using the VLOOKUP function to match and paste data from two sheets. Now that you understand how to use formulas to connect different spreadsheets, the next section will teach you how to manage multiple relationships between spreadsheets with the help of a relational database.