Vlookup where a number is stored as text

[howard]

I have the following Vlookup formula =VLOOKUP(A50,'COS recon'!A:C,2,FALSE), which returns #N/A

The reason for this is that the numbers on sheet COS recon are stored as text and A50 is stored as a number

I would like the Vlookup amended so that If the numbers are stores as text on shhet COS Recon and typed as numbers on the sheet using the lookup formula, it will returmn the correct information

Your assistance in this regard is most appreciated

 

[Aladin Akyurek]

I have the following Vlookup formula =VLOOKUP(A50,'COS recon'!A:C,2,FALSE), which returns #N/A

The reason for this is that the numbers on sheet COS recon are stored as text and A50 is stored as a number

I would like the Vlookup amended so that If the numbers are stores as text on shhet COS Recon and typed as numbers on the sheet using the lookup formula, it will returmn the correct information

Your assistance in this regard is most appreciated

Try...

=VLOOKUP(A50+0,'COS recon'!A:C,2,0)

FALSE can be replaced with 0 in such formulas for they mean the same thing, i.e., the exact match.

 

[howard]

Hi Aladin

Thanks for the reply. The formula still returns #N/A. The numbers on theCOS Recon sheet are stores as text in Col a and the numbers on the sheet looking up the Vlookup are entered as numbers for eg A50 is a number

It would be appreciated if you would amend your formula to accomodate this, possibly incorporating the Vlookup with an ISNUMBER or Istext

 

[Aladin Akyurek]

Hi Aladin

Thanks for the reply. The formula still returns #N/A. The numbers on theCOS Recon sheet are stores as text in Col a and the numbers on the sheet looking up the Vlookup are entered as numbers for eg A50 is a number

It would be appreciated if you would amend your formula to accomodate this, possibly incorporating the Vlookup with an ISNUMBER or Istext

=VLOOKUP(A50&"",'COS recon'!A:C,2,0)

would convert A50 into a text-number. Looks like you want this happen for 'COS recon'!A:A houses text numbers.

 

[howard]

Hi Aladin

Thanks for the help, formula returns desired result

Regards

Howard

 

[Aladin Akyurek]

Hi Aladin

Thanks for the help, formula returns desired result

Regards

Howard

You are welcome. Thanks for providing feedback.

 

[Biz]

=VLOOKUP(A50&"",'COS recon'!A:C,2,0)

would convert A50 into a text-number. Looks like you want this happen for 'COS recon'!A:A houses text numbers.

Nice little trick. I usually use =VLOOKUP(TEXT(A50,0),'COS recon'!A:C,2,0)

Biz

 

[I'm Splatman]

Just used this trick for same problem, very handy, thanks Aladin

 

[Aladin Akyurek]

Just used this trick for same problem, very handy, thanks Aladin

Cool. Thanks for providing feedback.

 

[Miodrag]

Thank you both Aladin and Biz even more, since his solution did it for my problem...

Aladins solution works well when you have only numbers stored as text, but when you have both numbers and text in the column you're looking in, and you stored all as text so it would be of the same type, and you're typing on keyboard 101, 250 and so on excel would see it as numbers and won't find exact match... such as my data it starts with 001, 002 ... 100, 101, 102... 1001, 1002 ... while it ends with S4522013, S4372013...

Solution that Biz provided works perfectly: VLOOKUP(TEXT(B3;0);'katalog 2013 - 2014'!$A$2:$B$1008;2;FALSE) , many thanks again!