# VLOOKUP function not working properly for numbers formatted as text

#### mr_bubblez

##### New Member
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
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
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
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
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")

Last edited:

Replies
1
Views
218
Replies
2
Views
335
Replies
3
Views
70
Replies
1
Views
150
Replies
1
Views
831

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.

### Which adblocker are you using?

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

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