Find nth biggest match when adjacent cell isn't empty

cpuglia

New Member
Joined
Jul 2, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

X160
X130
140
110
X120
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.

Many thanks in advance.
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,490
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
Joined
Jul 2, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,490
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
Joined
Jul 2, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
The good news is all of my values will be unique, so MINIFS is working perfectly. Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,985
Messages
5,575,381
Members
412,658
Latest member
LS0009
Top