Vlookup not taking a concatenated value as lookup_value

[WRP]

Hi all,
New member here and I have a problem in this formula =VLOOKUP(I952,SourceData4!$A$2:$K$150,2,FALSE). The contents of cell I 952 is itself a concatenated 5 digit number. Returns a #NA error. If, however, I enter the 5 digit number instead of allowing it to be concatenated, the Vlookup function works correctly.
Any ideas? all cells have been formatted as numbers.
Roy

[benishiryo]

hi Roy, welcome to the forum. i am guessing A2:K150 is containing numbers. when you concatenate, you are making it a text. you can try:
=VLOOKUP(value(I952),SourceData4!$A$2:$K$150,2,FALSE)

upload an sample if it doesnt work

[Pete_UK]

Yes, but if you concatenate two numbers they are treated as text values, so try it like this:

=VLOOKUP(--I952,SourceData4!$A$2:$K$150,2,FALSE)

The double-minus in front of I952 will convert that value to numeric - you could also multiply by 1 or add zero to do the same (or even put VALUE( ... ) around it).

Hope this helps.

Pete

[WRP]

Thanks folks, Wow, that was fast. I tried multiplying the concatenated value by one. No joy. In fact now the formula does not seem to be calculating at all. Only see the formula in the cell, even though options are set for automatic calculation.

[Pete_UK]

Sounds like that cell has become formatted as Text. Format it as General, then double-click as if to edit it and then press <Enter> to bring about the change in format.

Hope this helps.

Pete

[WRP]

Thanks Pete !!! Perfect, worked!! This has been a very helpful (first) experience for me. I'll be back, for sure.