lookup count

A

Antonio

I am using LOOKUP to find an exact match on a range that is not sorted.

Is there a way to know if there is more than one match?

I would prefer a formula rather than programming via VBA.

Thanks,

Antonio
 
G

galimi

You can enter a formulaic array that sums up all the occurences of the item
you are looking for:

=sum(if(a1:a100="test",1,0))

Press control-shift-enter after the last closing parenthesis to indicate it
is a formulaic array. Replace a1:a100 with the range you are search and the
word test with the lookup command or the item you are looking for. This will
give a count of the number of items you are looking for in your range.
 
A

Ardus Petus

Assuming your VLOOKUP formula is in A1, and the lookup array in C1:D99
=COUNTIF(C1:C99,A1)>1
That will return TRUE if there is at least 1 dupe.

HTH
 
A

Antonio

Thank you Ardus and Galimi, both solutiions work great.

The countif is the simplest one.

Regards,

Antonio
 
Top