Using LOOKUP only if a value exists

  • Thread starter Thread starter Android
  • Start date Start date
A

Android

Hi

I am trying to use the LOOKUP function to find a value in column x, when a
data item in column y equals my test value.

However if my test value does not exist in column y, LOOKUP gives the wrong
data. As Excel help states: " If LOOKUP can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or equal to
lookup_value."

How can I use an IF clause to return "0" when there is no match?
 
Android,

Here might be one solution. From XL help.
Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

If you set range_lookup to TRUE, then it will return #NA if it can't find
the value.

So you can take advantage of that.

=If(ISNA(Vlookup(your arguments)), 0, Vlookup(your arguments))

Effectively, if there is no match, then vlookup goes to #NA. If NA, then 0.
Else, evaluate your vlookup.

HTH.

Regards,
Kevin
 
Please correct me if I am wrong, but shouldnt Android be using the FALS
range_lookup.

Using TRUE will give him what he has now. Using FALSE will produce th
desired #N/A.

If that is used in the ISNA statement it should give the correc
result, where using TRUE will not
 
Hi Frank and Rusty,

You are absolutely correct. I should have written False as the last
argument.

Best regards,
Kevin
 
Back
Top