Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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