Vlookup N/A# error and VALUE# error

  • Thread starter Thread starter fitzsociety
  • Start date Start date
F

fitzsociety

I have a list of part numbers that contain text and numbers, I am trying to
pull data from another worksheet over by using the part number as my common
entity. However I keep getting either a N/A# error or a #VALUE error. The
two functions that I am using are:
=VLOOKUP(--F3&"",Table!$B$3:$D$957,2,FALSE) and
=VLOOKUP(--F3,Table!$B$3:$D$957,3,FALSE). I am using the first function to
pull data from the second column on the table worksheet into the G3 cell. I
am using the second function to pull data from the third column on the table
worksheet into the H3 cell. Please help I am over my deadline as of today.
If you need to see the data yourself I can e-mail it to you.
 
Why are you using the minuses?

If F3 is text the minuses will create the value errors

if they are not text the &"" will create the N/A error
if column B has numbers

Make sure what's in B3:B957 are numbers by
removing any trailing invisible or leading characters

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


install the TRIMALL macro


http://www.mvps.org/dmcritchie/excel/install.htm



then select B3:B957 and run the macro, assuming you have invisible
characters the values in B will be numbers

then use


=VLOOKUP(F3,Table!$B$3:$D$957,2,FALSE)



and



=VLOOKUP(F3,Table!$B$3:$D$957,3,FALSE)




--


Regards,


Peo Sjoblom
 
In your first formula you are trying to ensure that F3 is a number (by
--F3) and then making it into text by joining "" onto it, so there are
a couple of things that could go wrong here. If F3 does not only
contain numbers then --F3 will produce a #VALUE error, but as you are
trying to convert it into text eventually you could just have
VLOOKUP(F3&"", ... etc.

In your second formula the same comments apply to --F3 if it contains
letters as well as numbers, and this does not match with your first
lookup.

You will get the #N/A error if there is not an exact match in column B
of your table.

The following should enable you to check for both text and numeric
entries in your table and avoid errors:

=IF(ISNA(MATCH(F3&"",Table!$B$3:$B$957,0)),IF(ISERROR(--F3),"not
present",IF(ISNA(MATCH(--F3,Table!$B$3:$B$957,0)),"not
present",VLOOKUP(--F3,Table!$B$3:$D$957,2,FALSE))),VLOOKUP(F3&"",Table!
$B$3:$D$957,2,FALSE))

Use a similar formula in the next cell, and just change the ,2, to ,3,
in two places. You might like to change the "not present" message to
something else.

Hope this helps.

Pete
 
Back
Top