vlookup returning a #N/A value

[Guest]

Problem with vlookup returning #N/A value in only some of the worksheet
I have an inventory of some 4,000 items
The data I'm looking for is in the target worksheet, but vlookup returns
#N/A for some of the results.
I'm wondering if it has to do with the format of item numbers (which are a
combination of numbers & letters).
Would it make a difference if some was number & some text??

 

[Guest]

Hi Ian,
Mixing text and number formats will create problems.
Try converting both the source and destinaiton columns to text.
Select the entire column and go to Data > Text to Columns...
In that dialog box click next until you get to Step 3 and select text.
See if that works for you.

 

[Richard Buttrey]

Problem with vlookup returning #N/A value in only some of the worksheet
I have an inventory of some 4,000 items
The data I'm looking for is in the target worksheet, but vlookup returns
#N/A for some of the results.
I'm wondering if it has to do with the format of item numbers (which are a
combination of numbers & letters).
Would it make a difference if some was number & some text??

Yes indeed. If you try to lookup a number in a range which contains
the 'number' as text, then you'll get #N/A.

Depending on your data, you could try converting all your target
numbers to numbers proper with =Value(cellref)

HTH

RB
__

 

[Pete_UK]

If you want to avoid the #N/A error, you could replace your lookup
formula with:

=IF(ISNA(your_formula),0,your_formula)

or you could have "" instead of the zero in the middle.

Hope this helps.

Pete

 

[Aladin Akyurek]

Run the TrimAll macro on your table in order to remove possible unwanted
chars around entries. You can track down the macro by means of Google.

 

[Dave Peterson]

Just some more help:

David McRitchie has a macro that cleans up this kind of stuff:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()"

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm