1

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.

1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.