AutoFill

New Contributor

Hi, 

How do I autofill a formula that should take the data from e.g. colum A:B, C:D, E:F, etc? I tried to fix the column ($A:$B, $C:$D, $E:$F) but the pattern was not recognized, instead it gave me back A:B, B:C, C:D, etc. 

Thanks 

4 Replies

@SabPFSTR 

You can probably use the INDEX function, but I'd need more info about the formula you want to create and where the formula will be entered.

@Hans Vogelaar many thanks. This is a vlookup formula in sheet2 while this sheet has the date in column A, the stock1 in column B where I want to match the value to the date. Sheet1 contains the raw data. In column A of sheet 1 is the date, B the stock1 with the respective values, then C contains again the date, D the values for the stock2 etc. Now when I enter the vlookup formula in sheet2 cell b2 vlookup($a2;sheet2!a:b;2;false) and use autofill, it searches for sheet2!b:c and not c:d. 

IMG_4595.jpg

@SabPFSTR 

In B22:

 

=IFERROR(VLOOKUP($A22;OFFSET(Data!$A:$B;0;2*COLUMN()-4);2;FALSE);"")

 

Fill to the right (and down)

Great, many thanks! Even a smarter solution