why the error message?

[sumesh56]

in this formula why comes the error message at the last col. till the last column it worked perfect. kindly explain.

[[email protected]]

Error was giving because you had selected only 10 columns for vlookup and your column() formula was given 11 number so i have changed only column()-1 and that workes. Please check

Regards,
Suhas

[TMS]

Because the range B:K only has 10 columns and you are in the 11th column (using COLUMN()).


Regards, TMS

[TMS]

I agree with Suhas.

And your formula isn't consistent. In some cases it only goes down to Row 20, not Row 21.

Copy down and across: C8

Please Login or Register  to view this content.

[Chris 53]

It shows a reference because you are telling it to use column() from column K which resolves to 11, but the table you are looking in starts at B and has only 10 columns till it gets to column K. So your vlookup formula is reading past the end of the vlookup table.

If you look carefully you will see that all of your results are 1 column out, change the vlookup formula to be =VLOOKUP($B8,'Form-II'!$B$7:$K$20,COLUMN()-1,0), that should resolve your problems.

[TMS]

@Chris 53: in fact, the table goes to row 21 so, if you copy that formula down it will fail on the last line.

Regards, TMS