Login
Combining Data From Two Spreadsheets


If you have data in one spreadsheet you want to append onto matching records in another spreadsheet, you can use the VLookup function.  Here's the requirements:

  1. Both spreadsheets must have a unique identifier (like voter ID) to match on.
  2. The spreadsheet you are pulling data from must have that identifier column to the left of the column you want to append, so move it if it is not.  (It does not have to be the immediate column to the left.)

Here is the formula to use.  It includes additional tricks to suppress errors and 0s that will otherwise be inserted.

=IFERROR("" & VLOOKUP (H2 , 'ssWithData'!$J$2:$O$345 ,3,FALSE ) ,"")

  • Place the formula in the first cell of the main spreadsheet that you want to display the new values.  
  • "H2" is the cell on this row that has the unique ID value to match.
  • "ssWithData" is the name of the 2nd spreadsheet that has the data we want to pull from
  • $J$2 is the first row in that 2nd sheet that has the unique ID values that will try to be matched to the "H2" cell of the main row.
  • $O$345 is the last row and extending as far to the right as needed to include the column that has the date you want to insert when a match is found.
  • Note the $ is used in the preceding values to prevent that range from changing as the formula is copied to different cells in the main spreadsheet.  We do not want it to change because it defines all cells that are needed in the 2nd spreadsheet to both find the matching unique ID and supply the desired value.
  • "3" means you want to insert the value from the 3rd column of the cell range that you just defined when a match is found.  Since this example starts with column "J" for the cell range, it will insert the values of column "L".  Column "J" = 1, "K" = 2, "L" = 3.
  • "FALSE" - confusing to explain so just always enter FALSE or you'll get wrong results for some rows.
  • The VLOOKUP formula is wrapped in an "IFERROR" function to prevent displaying "N/A" when a match can not be found.
  • The VLOOKUP formula is preceeded with a ...
    "" &
     ... to prevent a 0 being displayed when a match is found but the value column you are wanting to copy is blank.


TIP: After spot checking results to verify values are correct , copy the new columns that contain the formula and paste them into a new column using the "Paste Special" option, then select "Values".  That will give you new columns of only the cell contents without the formula ensuring things won't get messed up during later sorting/moving activities.  Then go back and delete the columns you added that have the formula.  This way you're main spreadsheet will only contain the data values and not be using the formulas which will both speed up the spreadsheet and minimize risk of data corruption if the 2nd spreadsheet is modified or deleted.

Last updated by Doug Homan, created February 20, 2015