Copying data, based on the value in a cell

Question

I've been trying to work out how to copy data from cells on a worksheet into another based upon the value in a cell.

We have weekly data so for arguments sake

Week 1     Week 2     Week 3     Week 4

   1         5          3          2

  $10       $90        $75        $15

What I'd like to be able to do, is input the overall worksheet with Week 1 and it automatically drags 1 and 10 into cells from the data worksheet.

I've been trying to make lookup address & vlookup work, but neither appear to do what I'd like them to... I'm using Google Docs if that makes any difference?

Answer 1

If Week 1 is in A1 of both sheets, 1 is in A2 of a worksheet etc then this might suit in B1 of another:

 =index(a!$A:$D,2,match($A1,a!1:1,0))  

copied across to C1 and the 2 there replaced by 3.