Finding Next Largest Number Based on Criteria
 

I have a problem that is stumping me. In cells A1 through A10, I have numbers 1-10. In cells B1-B10 I have corresponding identifiers, Apples, Oranges, Pears. It looks like this

A B

1 Apples
2 Apples
3 Oranges
4 Oranges
5 Apples
6 Pears
7 Oranges
8 Pears
9 Oranges
10 Apples

I need a formula that I can copy down that looks in column B for "Oranges" and returns the corresponding number that is next largest above the number it already found. The result would look like below...

3
4
7
9

Any feedback would be most helpful. Thank you

Finding Next Largest Number Based on Criteria
 

There may be an easier way, but this late into the night this is the method I came up with. Assuming your data is in cells A1:B10, you would enter this as an array formula (CTRL+Shift+Enter)into cell C1 and copy it down to cell C10.

=IF(COUNTIF($B$1:$B$10, "Oranges")= ROW(A1), SMALL(IF($B$1:$B$10="Oranges", $A$1:$A$10, 10^308), ROW(A1)), "")

Good luck

Ben

Finding Next Largest Number Based on Criteria
 

On Wednesday, February 27, 2013 9:02:32 PM UTC-8, Ben McClave wrote:
There may be an easier way, but this late into the night this is the method I came up with. Assuming your data is in cells A1:B10, you would enter this as an array formula (CTRL+Shift+Enter)into cell C1 and copy it down to cell C10.



=IF(COUNTIF($B$1:$B$10, "Oranges")= ROW(A1), SMALL(IF($B$1:$B$10="Oranges", $A$1:$A$10, 10^308), ROW(A1)), "")



Good luck



Ben

It works! Thank you Ben

Finding Next Largest Number Based on Criteria
 

No problem, I'm happy to help.