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

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.
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"Antonio" wrote:

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

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
--
AP

"Antonio" a écrit dans le message de
news: ...
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

Thank you Ardus and Galimi, both solutiions work great.

The countif is the simplest one.

Regards,

Antonio

"Ardus Petus" wrote:

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
--
AP

"Antonio" a écrit dans le message de
news: ...
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