Finding Next Largest Number Based on Criteria

[djc]

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

 

[Ben McClave]

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

 

[djc]

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

 

[Ben McClave]

No problem, I'm happy to help.