Random selection with multiple column criteria

example.xls
Hello!

I am trying to go one step further than the query and answer found in this thread:
http://www.excelforum.com/excel-gene...om-symbol.html

What I want to do is randomly pull from a field of data, MINUS any cells that do not meet the criteria in other columns.

So if column A is my target column for the random selection (containing 10 unique entries), and column B has 10 repeating colors, and column C has 10 repeating animals, what can I use to perhaps create this effect:
A random pull from column A, but only from rows in which column B says red and column C says cat? (The pull being 1, 4, 7, or 9 in the below example, of course.)

Data (this example is also attached in an excel doc):
One Red Cat
Two White Dog
Three Blue Horse
Four Red Cat
Five White Cat
Six Blue Cat
Seven Red Cat
Eight Red Dog
Nine Red Cat
Ten Red Horse

I have tried variations on IF and INDEX to no avail. Perhaps something with SUMPRODUCT?

-k

Many thanks to the wise contributors in advance- this may be my first post, but only because I've been able to find all my previous questions already answered in previous threads!

I assume you're using a cell to indicate "red" and another for "cat". So let's add those into the mix.

column A = numbers
column B = colors
column C = animals
column D =RAND()
...copy that formula down the whole set

E1 = "red"
F1 = "cat"
G1 = random choice from column a with the formula:
=INDEX($A$1:$A$10, MATCH(SMALL(IF($B$1:$B$10 & $C$1:$C$10 = $E$1 & $F$1, $D$1:$D$10), 1), $D$1:$D$10, 0))

EDIT: That's an array formula, so you have to enter it and confirm with CTRL-SHIFT-ENTER

Originally Posted by kleick

IF determines an abridged array based on the criteria being met,
SMALL selects the lowest of the random values in that array,
MATCH indicates what row the selection is in based on position,
and INDEX determines what text value to display in the target column.

Very good, grasshopper. (old Kung Fu joke)

I'm not sure I could have summarized that as briefly as you did. Well done.