Using LOOKUP only if a value exists

[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?

 

[Kevin Stecyk]

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

 

[Rusty_nl]

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

 

[Frank Kabel]

Hi Kevin
I think you menat FALSE (or 0) as the 4th parameter

 

[Kevin Stecyk]

Hi Frank and Rusty,

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

Best regards,
Kevin

 

[Android]

Kevin, Frank, Rusty,

Thank you all very much. It works great.

Android.