# Find nth biggest match when adjacent cell isn't empty

#### cpuglia

##### New Member
Hello. I'm trying to write a set of formulas to find the nth next biggest number from an unsorted range, but only if the adjacent cell isn't blank.

 X 160 X 130 140 110 X 120 100

So in the example above, if I put 105 into a cell (lets call it C1), i want C2 to return 120, C3 to return 130, and C4 to return 160. C1,C2, C3, C4 are not adjacent in my actual sheet if that matters. I was using =SMALL(B5:B20, COUNTIF(B5:B20, "<"&C1)+1) to make it work when I didn't have to account for if Column A was blank or not (and incrementing the "+1" to get the next highest match). But now I can't wrap my head how to exclude values where column A is blank.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
If you have the dynamic functions, how about
+Fluff New.xlsm
ABC
1X160105
2X130120
3140130
4110160
5X120
6100
Compliance
Cell Formulas
RangeFormula
C2:C4C2=SORT(FILTER(B1:B6,(B1:B6>C1)*(A1:A6<>"")))
Dynamic array formulas.

#### Eric W

##### MrExcel MVP
Since you said the result cells aren't adjacent, then maybe:

Book1
ABCDE
1X160105
2X130120
3140130
4110160
5X120
6100
Sheet13
Cell Formulas
RangeFormula
C2C2=MINIFS(\$B\$1:\$B\$6,\$A\$1:\$A\$6,"X",\$B\$1:\$B\$6,">"&C1)
D3,E4D3=MINIFS(\$B\$1:\$B\$6,\$A\$1:\$A\$6,"X",\$B\$1:\$B\$6,">"&C2)

But it would mean manually changing the formula for each case, unless you have some kind of rule that defines where they are relative to each other.

#### cpuglia

##### New Member
Hi Eric. I'm away from my computer for a few minutes, but I think that will work as long as that function will accept <>"" as the criteria on the A1:A6 range, since it might not always be literally "X" in those cells. The results cells are always fixed locations, and i only need to find up to the 4th match above the input. So im fine adjusting manually.

Fluff, thank you for your input. I'll look into that too when I'm back in front of my sheet.

#### Eric W

##### MrExcel MVP
Yes, "<>" will work as a criterion. One thing to keep in mind is that if you have ties in your range, the MINIFS function might end up skipping one of the tied values. If that could happen, you could use AGGREGATE:

Book1
ABCDE
1X120105
2X130120
3140120
4110130
5X120
6100
Sheet13
Cell Formulas
RangeFormula
C2C2=AGGREGATE(15,6,\$B\$1:\$B\$6/(\$A\$1:\$A\$6<>"")/(\$B\$1:\$B\$6>\$C\$1),1)
D3D3=AGGREGATE(15,6,\$B\$1:\$B\$6/(\$A\$1:\$A\$6<>"")/(\$B\$1:\$B\$6>\$C\$1),2)
E4E4=AGGREGATE(15,6,\$B\$1:\$B\$6/(\$A\$1:\$A\$6<>"")/(\$B\$1:\$B\$6>\$C\$1),3)

Just change the last parameter (1 or 2 or 3) for the instance you need in each formula.

#### cpuglia

##### New Member
The good news is all of my values will be unique, so MINIFS is working perfectly. Thanks again!

Replies
10
Views
324
Replies
7
Views
122
Replies
22
Views
1K
Replies
23
Views
2K
Replies
12
Views
594