Yes, the formula in A will be copied and pasted to all the other cells. Only certain parts of the range have absolute references, so the formula will adapt as you move it around.

The same formula used in A1 copied into C5 will say:

=VLOOKUP(Sheet1!$A5,Sheet2!$A$1:$G$90000,COLUMN(D5)-1,0)

and it will pull the value of Sheet1!A5, and pull the corresponding data from the 4th column over on Sheet2.