Find nth biggest match when adjacent cell isn't empty

[cpuglia]

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.

Many thanks in advance.

 

[Fluff]

Hi & welcome to MrExcel.
If you have the dynamic functions, how about

+Fluff New.xlsm
	A	B	C
1	X	160	105
2	X	130	120
3		140	130
4		110	160
5	X	120
6		100
Compliance

Cell Formulas
Range	Formula
C2:C4	C2	=SORT(FILTER(B1:B6,(B1:B6>C1)*(A1:A6<>"")))
Dynamic array formulas.

 

[Eric W]

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

Book1
	A	B	C	D	E
1	X	160	105
2	X	130	120
3		140		130
4		110			160
5	X	120
6		100
Sheet13

Cell Formulas
Range	Formula
C2	C2	=MINIFS($B$1:$B$6,$A$1:$A$6,"X",$B$1:$B$6,">"&C1)
D3,E4	D3	=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]

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]

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
	A	B	C	D	E
1	X	120	105
2	X	130	120
3		140		120
4		110			130
5	X	120
6		100
Sheet13

Cell Formulas
Range	Formula
C2	C2	=AGGREGATE(15,6,$B$1:$B$6/($A$1:$A$6<>"")/($B$1:$B$6>$C$1),1)
D3	D3	=AGGREGATE(15,6,$B$1:$B$6/($A$1:$A$6<>"")/($B$1:$B$6>$C$1),2)
E4	E4	=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]

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