software

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 amp; letters).
Would it make a difference if some was number amp; 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 gt; 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 Hudsonquot;Ianquot; wrote:

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

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

gt;Problem with vlookup returning #N/A value in only some of the worksheet
gt;I have an inventory of some 4,000 items
gt;The data I'm looking for is in the target worksheet, but vlookup returns
gt;#N/A for some of the results.
gt;I'm wondering if it has to do with the format of item numbers (which are a
gt;combination of numbers amp; letters).
gt;Would it make a difference if some was number amp; 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 quot;quot; instead of the zero in the middle.

Hope this helps.

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

Just some more help:

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

www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for quot;Sub Trimall()quot;

If you're new to macros, you may want to read David McRitchie's intro at:
www.mvps.org/dmcritchie/excel/getstarted.htm
Aladin Akyurek wrote:
gt;
gt; Run the TrimAll macro on your table in order to remove possible unwanted
gt; chars around entries. You can track down the macro by means of Google.
gt;
gt; Ian wrote:
gt; gt; Problem with vlookup returning #N/A value in only some of the worksheet
gt; gt; I have an inventory of some 4,000 items
gt; gt; The data I'm looking for is in the target worksheet, but vlookup returns
gt; gt; #N/A for some of the results.
gt; gt; I'm wondering if it has to do with the format of item numbers (which are a
gt; gt; combination of numbers amp; letters).
gt; gt; Would it make a difference if some was number amp; some text??

--

Dave Peterson