vlookup returning a #N/A value

  • Thread starter Thread starter Guest
  • Start date Start date
G

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??
 
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.
 
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??
Click to expand...

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