RD,

Thanks for the response.

I put in the formula (by doing CSE) and for a particular product am
getting a value of zero. (it should be non-zero). I used the evaluate
formula feature to step in to it and notice that ROW($1:$10) always
evaluate to a value of 10. I believe that Rows($1:$10) should retrun an
array of values from 1 to 10 out of which depending on whichever of the
cases Z1:Z10=P27, the corresponding row numbers would be returned.
Please let me know in case am wrong in my supposition.

Maybe someone can come up with something better, but in the mean time, try
this *array* formula for *2* matches in AA1 to AA10

Does the above statement mean that the present CSE formula would return
correct values only if the smaller Lookup table has atmost 2 repeating
values?


Also, a doubt little unconnected to my goal here. I see that the array
part of IF condition evaluates to True and False and when the number 10
gets multiplied by 10 then False remains as false while True changes to
10. I have 2 questions he-
a) Why is False not changing to zero when multiplied by 10 but true
changes to 10 when mutliplied by 10
b) When we apply the SMALL function on a set of array values containing
FALSE and some positive numbers, why is the function not returning
False or Zero as the answer. Presently it returns the smallest positive
number.

Regards,
HP
India