# Finding Next Largest Number Based on Criteria

D

#### 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

B

#### 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

D

#### 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

B

#### Ben McClave

No problem, I'm happy to help.