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??

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.
--
Ken Hudson


"Ian" wrote:

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??

On Mon, 15 May 2006 15:48:01 -0700, Ian
wrote:

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
__

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

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.

Ian wrote:
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??

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



Aladin Akyurek wrote:

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.

Ian wrote:
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??

--

Dave Peterson