VLOOKUP function not working properly for numbers formatted as text

mr_bubblez

New Member
Joined
Sep 12, 2008
Messages
38
I have a column of "numbers" formatted as text. I have about 80,000 of these records that is referencing another sheet. VLOOKUP works properly on some records but is not working on about 1/3 of the records. These numbers are 16 digits long and some of them start with a zero (and often multiple zeros). I can't convert these data to numbers because I will lose the leading zeros and the numbers are too large for excel to perform calculations on so it automatically converts them to scientific notation. From my research, VLOOKUP often doesn't work properly when numbers are formatted as text, but I need them to be formatted as text. Does anybody have any ideas on a workaround for this? If it helps, here is my formula:

=IF(ISNA(VLOOKUP(B2,NAL!$C$2:$G$430749,4,FALSE)),"",VLOOKUP(B2,NAL!$C$2:$G$430749,4,FALSE))

ANY help will be GREATLY appreciated. Thank you!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Are the numbers in column C ALL "Numbers stored as text", or just "some" or "most" of them?

If they are ALL, then perhaps you can use this

=IF(ISNA(VLOOKUP(B2&"",NAL!$C$2:$G$430749,4,FALSE)),"",VLOOKUP(B2&"",NAL!$C$2:$G$430749,4,FALSE))


Or even this

=IF(ISNA(VLOOKUP(B2,NAL!$C$2:$G$430749+0,4,FALSE)),"",VLOOKUP(B2,NAL!$C$2:$G$430749+0,4,FALSE))

IMPORTANT
This is now an array formula that requires CTRL + SHIFT + ENTER
After entering the formula, highlight the cell with the formula and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}
 
Last edited:

mr_bubblez

New Member
Joined
Sep 12, 2008
Messages
38
Hi jonmo,

I appreciate your help. Unfortunately, SOME of these fields are completely blank, others have just text, some are mixed (primarily numbers with a couple of alpha characters mixed in), but most are just numbers stored as text.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
OK, do this.

On the NAL sheet, put this formula in an available column
=ISNUMBER(C2)
And fill down to the end of the data.

Are they ALL False, or are any of them true?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,454
Can you format NAL! column C as text (the entire column) so all the values are text and then use something like
TEXT(B2, "@")
or
TEXT(B2, "000000000000")
as your lookup?
 
Last edited:

Forum statistics

Threads
1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

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