Column Index Number Increment automatically in VLOOK

[ahmad08]

Hi Guys,
I have to drag down Vlookup formula in a way that col index should increase automatically. Formula I am using is
=VLOOKUP(A$2,Sheet1!A$1:D$6,2,TRUE)

When I drag it down, it still remains same

=VLOOKUP(A$2,Sheet1!A$1:D$6,2,TRUE)

Below is file on which I am working on.

VLOOKUP Column Index Problem.xlsx - Google Drive

 

[Scott T]

try

=VLOOKUP(A$2,Sheet1!$A$1:$D$6,ROWS(A$1:A2),TRUE)

 

[ahmad08]

Scott
Thanks a lot.

 

[MARZIOTULLIO]

SHEET2

B3=VLOOKUP(A$2,Sheet1!$A$2:$D$6,MATCH(Sheet2!A3,Sheet1!$A$2:$D$2,0),FALSE) copy down