Checking for multiple instances in an array

This is a good deal harder than my last issue. I have a few pieces of data:

A62:A80 - Array of Integers named X (2 through 20, to be exact)
D62:D80 - Array of Strings named Y (on a Drop Down)
AD45, AG45, AJ45 - Formula Holders

Now the tough part of explaining what I want... assume the string in question is "Pie":

• If Y contains "Pie" once, I want AD45 to take the value of the corresponding X.
• If Y contains "Pie" twice, I want AD45 to take the value of the first X, and AG45 to take the value of the second.
• If Y contains "Pie" three times, I want AD45 to take the value of the first X, and AG45 to take the value of the second, and AJ45 to take the value of the third.

So, in the end, the formula holders will either have a number between 2 and 20, or be blank. Note that it is possible for users to put more than 3 occurrences in Y even if it's user error.


I'm not really asking for the full formulas, but I simply don't know where to start on this, on what functions would be useful in pulling out the values I want.

[EDIT]
One idea I had is that:
AD45 searches the array for "Pie" and takes the first value of "X" that matches the find. Blank if it doesn't.

AG45 checks if AD45 is not blank, and searches the array for "Pie" starting at the next index.

AJ45 checks if AG45 is not blank, and searches the array for "Pie" starting at the next index.

Thanks, that definitely helped out a lot. I had to tweak it a bit, but now it's working to some extent. The only problem is that it's returning '2' (A62) no matter where it's finding the match.

I had to change:
$D$62:$D$80="Pie" to MATCH("Pie",D62:D80,0) before it would work, but I think using the MATCH function messes up the rest of the formula.

Bob's formula worked fine for me without mofification. Did you remember to array-enter it (Ctrl+Shift+Enter instead of just Enter).

If you're using Excel 2007, and the values in X (2,3,4,...) never change, you can shorten it some:

=IFERROR( SMALL( IF($D$62:$D$80 = "Pie", ROW($D$62:$D$80) ), 1) - ROW($A62) + 2, "")

... but it's still an array formula.

Your change will always return the first match.

You should pretend Excel doesn't support merged cells.