Vlookup and #N/A result

[Guest]

I have a master list of data and a shorter list (subset of the master) of
additional data. When I use vlookup including the FALSE argument, I get #N/A
for all data lines that don't exist in the subset list. When I use the TRUE
argument, I get incorrect results.

Both lists are sorted in the same order. The lookup value is alphanumeric.

How can I use a vlookup function and not get an #N/A result?
Once I get an #N/A result, I can't do any other mathematical functions on
that data.

 

[JE McGimpsey]

One way:

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2,FALSE))

 

[Guest]

You could try something like this:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

This first checks to see if your VLOOKUP formula returns an #N/A error, and
if so returns a blank. Otherwise, it returns the result of your VLOOKUP
formula.

HTH,
Elkar

 

[Dave Peterson]

If there's no match, what do you want to see?

Make the cell look blank?
=if(isna(vlookup(...)),"",vlookup(...))

return a 0?
=if(isna(vlookup(...)),0,vlookup(...))

 

[Guest]

=IF(COUNTIF(.....),VLOOKUP(.....),"")