Lookup function when 'lookup_value' does not always exists
 

I am using the LOOKUP funtion for an array. Of my 300+ values I know that
about 20 'lookup_value'(s) don't exists in the array, but I don't want it to
find the next closest value. How can I test my array to see if the
'lookup_value' exists?
Thanks,
Ken

Lookup function when 'lookup_value' does not always exists
 

One way:

A1 = lookup value

B1:B10 is lookup array

=IF(COUNTIF(B1:B10,A1),LOOKUP(......),"what do you want to happen if the LU
value doesn't exist")

--
Biff
Microsoft Excel MVP


"Ken King" wrote in message
...
I am using the LOOKUP funtion for an array. Of my 300+ values I know that
about 20 'lookup_value'(s) don't exists in the array, but I don't want it
to
find the next closest value. How can I test my array to see if the
'lookup_value' exists?
Thanks,
Ken

Lookup function when 'lookup_value' does not always exists
 

Consider using VLOOKUP...

if your values are in COL A and value to lookup is in B1 then use
=VLOOKUP(B1,A:A,1,False)
it will give you #N/A if the value is not found...
you can test with ISNA() which will give you TRUE when value is not found.
Values in Col A need not be sorted...

VLOOKUP with last parameter as TRUE gives you the closes match (and values
have to be sorted)

"Ken King" wrote:

I am using the LOOKUP funtion for an array. Of my 300+ values I know that
about 20 'lookup_value'(s) don't exists in the array, but I don't want it to
find the next closest value. How can I test my array to see if the
'lookup_value' exists?
Thanks,
Ken