VLOOKUP peculiar problem

[anuradhagrewal]

Hi
I have a peculiar problem while using VLOOKUP
The issue at hand is this
1)There are two sheets say Sheet 1 and Sheet 2
2)Now I have to look up variable in Row A2 in Sheet 1 for a given date in a specified range in Sheet 2.
3)So I do a this action for eg vlookup($A1, sheet2!$A2$:$F$1789,2,false).
4) Now the problem is that the variables $A1,$A2 etc and the range remain constant but the column to look up and paste the values in sheet 1 i.e "2" keeps changing as per date.

My problem is that everytime I copy the above formula across columns the value of col_index_num needs to be changed manually to lookup the range in that column of Sheet 2.
For eg in B1 the vlookup formula would be vlookup($A1, sheet2!$A2$:$F$1789,2,false)
For eg in B2 the vlookup formula would be vlookup($A1, sheet2!$A2$:$F$1789,3,false) and so on.

Please advice if there is anyway the col_index_num can incrementally increase automatically eg 2,3,4, etc.

Regards

Anuradha

 

[etaf]

try
vlookup($A1, sheet2!$A2$:$F$1789,row()+1,false)

In B1 , then the ROW() will be 1 , then 1 + 1 = 2
IN B2 , the row() will be 2 , then 2+1 = 3
etc

your range is A : F
so will only work
to row 5
row() + 1 , will = 6
and F is the final column to return from

 

[Peter_SSs]

My problem is that everytime I copy the above formula across columns the value of col_index_num needs to be changed manually to lookup the range in that column of Sheet 2.
For eg in B1 the vlookup formula would be vlookup($A1, sheet2!$A2$:$F$1789,2,false)
For eg in B2 the vlookup formula would be vlookup($A1, sheet2!$A2$:$F$1789,3,false) and so on.

I think that you are confusing rows/columns and/or across/down.
Above in red I have highlighted "across columns" but B1 and B2 that you then discuss are not across columns but down a single column.
Please clarify carefully.