Joining Sheets

Next we need to get information from one sheet to the next sheet – get the population counts into the budget sheet to be able to calculate spending per citizen.

To do this we’ll use a new formula called VLOOKUP – VLOOKUP helps you retrieve related information from other sheets and associate this information with the right row.

Walkthrough: Joining sheets with VLOOKUP

• Create a new column right of the last column we’ve created
• Next, let’s write our formula: VLOOKUP wants 4 parameters (the last one is most often 0)
• The first parameter is the term we’re searching. This is a term present in both sheets, which is used as a reference by the formula to retrieve the correct values. In our case this term is the name of the council.

Note: We assume here that the same council name does not appear in more than one region. In the general case, you should be really careful about assumptions you make when matching items, which is why we prefer matching on proper, unique identifiers. The longer the matched item (for example, council + region) the more likely the item is to be unique. However, the longer the matched item, the harder it is to get an exact match. For example, “City, Region” would not match “City , Region” because the second element contains a space before the comma that does not in the first.

• The second parameter is a selection of the cell range we want to bring matched data in from, as well as the range of values we want to import. In our case, this corresponds to the columns C and D of the second sheet: the first index column is the column that has to contain the term we’re searching for, the rest of the columns represent data we could import data from.
• The third parameter is the column to return from the range specified in the second parameter, counting from the first (index) column which we refer to as column number 1; in our case, we want to pull data in from column 2, the second column.
• The last parameter we set to the value 0 to indicate that we only want to match items on an exact match.
• Our formula looks like this: VLOOKUP(E2,Sheet2!C:D,2,0)

• If you press enter, you’ll see the value is filled into the cell – let’s extend this to all cells by filling downs as we did before, by double clicking on the small blue square in the lower right hand corner of the cell.
• Notice that there may be some cells containing unknown #N/A values when you scroll down – these are the fields that could not be matched. You can compare the first and second sheet to find out what’s wrong.
• Now we can calculate the Spending per person by dividing the budget column by the population column.