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.

One way:

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

In article ,
brb wrote:

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.

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


"brb" wrote:

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.

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(...))



brb wrote:

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.

--

Dave Peterson

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

"brb" wrote:

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.