Vlookup where a number is stored as text

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Jun 26, 2006
Messages
5,701
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi Aladin

Thanks for the help, formula returns desired result

Regards

Howard
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,677

ADVERTISEMENT

=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
 

Miodrag

New Member
Joined
Apr 20, 2014
Messages
1
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,909
Messages
5,598,796
Members
414,259
Latest member
beetle12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top