misbehaving Vlookup function

[Maudise]

Hi All,

Strange problem today, I have been building some Vlookup functions and one has decided to not work so to investigate I built it bit by bit.

The formula is =VLOOKUP('Sheet2'!$M$1, '[Workbook2.xlsx]Sheet3'!$A$383:$Y$383,Column('[Workbook2.xlsx]Sheet3'!$W$2,0)

so it looks up a value from another sheet and finds it in column A of another workbook within a given range, then returns column W from that array. This formula works fine, however when I extend the array out to $A2:$Y$383 it suddenly loses the ability to find the correct data item.

Does anyone have any ideas as to why this might be occuring? and no there are no duplicate entries in column A and all other elements seem to be working fine with the smaller range?

Regards
Maudise

[martindwilson]

well this bit does nothing except increment the column looked up
Column('[Workbook2.xlsx]Sheet3'!$W$2) it just = 23 and can be replaced with w1

also there are other things wrong /missing
i think it should probably look like this
=VLOOKUP('Sheet2'!$M$1, '[Workbook2.xlsx]Sheet3'!$A$1:$Y$383,Column(w1),false)

[Maudise]

Thanks, I appreciate that, however others have a habit of adding columns so I am hoping that it helps avoid the problem where adding a column throws out all the Vlookups! Not sure if it works yet though

Maudise