#VALUE! error in vlookup formula

[nsaraiva]

Hi,

I'm using a vlookup formula, using a description text as the lookup_value. I know it would be better to use some sort of reference number rather than a text description but this is the only unique matching I can do.

The formula works fine for about 70% of the rows, but in the remaining 30% I get a #VALUE! error. I've done some searching on this and other forums and the indicatinon I get is that it should be related to the data type on the lookup_value cell. This is defined as General for all lookup_value cells and for the first column of the table array. In other words if I'm doing the exact same thing for all rows why am I getting an error only in some?

Thanks in advance

 

[prince141987]

Hi Friend,

Can you please shere your workbook with us.

regards
Prince

 

[niladri2005]

Microsoft Excel may display the #VALUE! error if your formula includes cells that contain different data types.

 

[nsaraiva]

Thanks Prince,

Not sure if this is what you mean but hereis the formula I'm using:

VLOOKUP(D144,Comments!$E:$S,3,FALSE)

 

[nsaraiva]

Thanks,

I've checked this, and the data types are set the same across all cells (as general).

 

[sky1in5]

you are looking at column 3.. what for put till $s? $e:$g will do?

that what i think.. correct me if i am wrong..

 

[niladri2005]

can you please attach a sample set of data?

 

[nsaraiva]

can you please attach a sample set of data?

comercially sensitive information, sorry...

i did try to erase the text and type something random on both tables (like: asioufbhasuiofsduio), and now the formula does work! could it be that the text being compared is just too long? is there a limit to the number of characters a vlookup can compare?