Vlookup N/A# error and VALUE# error

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.
 
M

muddan madhu

try this

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

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

Peo Sjoblom

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
 
P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top