Drag and copy range vertically from spreadsheet google

Question

I want the range A:A changes to B:B , C:C when i try to drag & copy a formula vertically.

The only way i know how i could do this is by changing the formula manually but i don't want to do this because i have around 700 rows

Formula i'm using are ranges from another page

I want to copy the formula in many rows, but as you can see on the image i get the formula like in the left column. I want to achieve something like in the right column without having to modify the range manually. (In this example i removed the = to show you the formula)

I tried excel way but it doesn't work by trying to create 2 line one with A:A and second line with B:B and than select both lines and drag to copy lower lines but it just keeps on copy both ranges over and over.

Answer 1

Try this:

=MATCH("*"&$B$2&"*",INDIRECT("Data!"&CHAR(64+row(A2))&":"&CHAR(64+row(A2))),0)

CHAR(64+row(A2)) is the same as CHAR(66) which is B, as you drop it down, Char() increases by one so you get the next column. So CHAR(64+row(A3)) is the same as CHAR(67) which is C

Answer 2

This formula should also work in cell A2:

=IFERROR(MATCH("*"&$B$2&"*",INDIRECT("Data!"&ADDRESS(1,ROW())&":"&ADDRESS(ROWS(Data!A:A),ROW())),0))

I've set up an example sheet here: https://goo.gl/gyORkV