1.

2.

• VLOOKUP is an Excel function that can pull data from one worksheet to another, based on a primary key.

• Ex: given a spreadsheet of usage data and a spreadsheet of pricing data, prices can be pulled into the usage spreadsheet using the ISSN for each item.

3.

• Source spreadsheet: the spreadsheet from which you will pull data. (in our example, the pricing data)

• Destination spreadsheet: the spreadsheet into which you are adding data. (the usage data)

• Primary key: the column that uniquely identifies each row and is present in both spreadsheets. (the ISSN)

• Source table: the subset of the spreadsheet that contains both the primary key column and all columns from which you are pulling information.

4.

• Decide what column you are going to use for your primary key.

• • A value that is included in both spreadsheets

• • Unique for each row

• • ISSNs or other numeric identifiers are good

• • Titles or other long strings that can vary slightly are not as good, because it can be hard to match them exactly.

5.

• Identify your source table

• • A selection of your source spreadsheet

• • Contains all the information you want to pull into the destination spreadsheet

• • Also contains primary key column

• • It might be the entire source spreadsheet, or it might just be a few columns.

6.

• Make sure that your primary key column is the left-most column of your source table.

• • This might involve moving some columns around.

Source Table Primary Key Column

7.

• Copy the column titles for your source table from the source spreadsheet to the destination spreadsheet.

8.

• Click on the first cell of the first column in your destination spreadsheet where you would like to insert data from your source spreadsheet.

9.

• From the Formulas tab, under Lookup & Reference, choose VLOOKUP

10.

• The Function Arguments window opens.

• Click on the first input box (lookup_value), then click the box in your DESTINATION sheet that contains the PRIMARY KEY for that row.

Primary Key

11.

• Click in the Table_array box, then switch to the source worksheet and select the entire source table.

• You can now hand-edit the selection further if you need to in the box.

12.

• In the col_index_num box, put the column number that you want to pull data from in the source spreadsheet, not the letter.

• For example, if you want data from Column D, and your Primary Key (first column of your source table) is in Column B, youll put in 3. (column B is 1, C is 2, D is 3).

13.

• For Range_lookup, enter FALSE to indicate that only exact matches for the primary key should be returned. Click OK.

14.

• Once the formula is entered once, you can use fill down to finish the column, but first you need to fix some variables.

• Put a $ before the column (letter) label indicating lookup value.

• Put a $ before the column (letter) AND row (number) labels indicating source table

15.

• You can now use fill-down to pull in values for the rest of the column

• To use the formula for other columns, copy and paste the first row, changing only the col_index_num variable, then fill-down as necessary.