lookup count

[Guest]

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

 

[Guest]

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.

 

[Ardus Petus]

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

HTH

 

[Guest]

Thank you Ardus and Galimi, both solutiions work great.

The countif is the simplest one.

Regards,

Antonio