Google Sheets – Importing Columns of Data

google sheetsvlookup

I'm trying to import columns of data from different sheets. Examples:

SEARS -- (sheet one) (I'm entering the prices manually)

1/2013   $8
2/2013   $5
3/2013   $14

Here is sheet two: (I'm entering the prices manually)

TARGET
1/2013  $7
2/2013  $10
3/2013  $4

Here is the resulting sheet (to be imported automatically from the other 2 sheets).

TOTALS    SEARS    TARGET
1/2013    $8        $7
2/2013    $5        $10
3/2013    $14       $4

I'm not sure how to do this. I think that for the resulting sheet I could use the VLOOKUP function to look for a date value in each sheet. Something like: 

=VLOOKUP (1/2013, 'TARGET_SHEET'|B1:C3,1).

This produces the desired result for the cell, but ideally I would want a way to clone this formula to all the rows and columns on the result sheet so I don't need to fill it out manually for each cell.

Anyone have an idea about how to do this?

I am using the NEW Google sheets, by the way.

Best Answer

Ok, I think I've figured it out. Not straightforward, but now that it's done, not terribly difficult. 

**=VLOOKUP(D1,'TARGET_SHEET'|A:B,2)**

Some explanations about why I couldn't get the answer.

  • Criterion (the D1 here) does not need to be a string. It can be a cell reference.
  • A1 notation for cells can be used to refer to complete columns (did not know that). So A:B is perfect syntax in A1 notation. http://msdn.microsoft.com/en-us/library/office/bb211395%28v=office.12%29.aspx
  • The array by definition needs to be 2 or more columns. (duh!) The value for the index parameter will likely be "2" (for a two column array). If the array had 3 columns, more than likely the value would be 2 or 3.
  • You can auto-increment the values for this vlookup formula by grabbing the handlebars and pushing them down on the result column. So: in the cell immediately below the one with the formula, you will see =VLOOKUP(D2,'TARGET_SHEET'|A:B,2)
Related Topic