VLOOKUP function not working properly for numbers formatted as text

M

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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
Variable decimal format: displayed value differs from the value used when referenced in another formula
Replies
4
Views
296
Peter_SSs
Peter_SSs
F
  • Question
I'm trying to enter barcodes but it is not letting me change the cell format from text to number
Replies
3
Views
167
Georgiboy
Georgiboy
B
  • Question
Number format with 6 digits from import without leading zeros
Replies
4
Views
512
bkeebaugh
B
R
  • Question
VLOOKUP formula using the TEXT function as the "lookup value"
Replies
1
Views
652
awoohaw
awoohaw
M
  • Question
formatting number as text
Replies
5
Views
263
Fluff
Fluff

Forum statistics

Threads
1,214,838
Messages
6,121,885
Members
449,057
Latest member
Moo4247

Share this page

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
Back
Top