find nth larger number (that is not blank) and nth smaller number (that is not blank)

[ellison]

Hi, have been searching around but can't quite find something that I can apply to an issue we would like to crack!

We are trying to find a way to find the closest 3 smaller numbers and closest 3 larger numbers in our "Raw Data" compare to our "Work in Progress Data".

I've set out a working example below which shows the "Raw Data" and "WIP data" and what the answers should hopefully be.

(If it helps, we are on Microsoft 365 and the list should be about 20K rows.)

Raw Data
1
3
7
15
20

WIP_Data	Next Higher in the Raw Data	2nd Higher in the Raw Data	3rd Higher in the Raw Data	Next Smaller in the Raw Data	2nd Smaller in the Raw Data	3rd Higher in the Raw Data
1	3	7	15	#n/a	#n/a	#n/a
2	7	15	20	1	#n/a	#n/a
6	7	15	20	3	1	#n/a
14	15	20	#n/a	7	3	1
18	20	#n/a	#n/a	15	7	3
25	#n/a	#n/a	#n/a	20	15	7

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E	F	G	H	I
1	Raw Data		WIP_Data	Next Higher in the Raw Data	2nd Higher in the Raw Data	3rd Higher in the Raw Data	Next Smaller in the Raw Data	2nd Smaller in the Raw Data	3rd Higher in the Raw Data
2	1		1	3	7	15	#NUM!	#NUM!	#NUM!
3	3		2	3	7	15	1	#NUM!	#NUM!
4	7		6	7	15	20	3	1	#NUM!
5	15		14	15	20	#NUM!	7	3	1
6	20		18	20	#NUM!	#NUM!	15	7	3
7			25	#NUM!	#NUM!	#NUM!	20	15	7
Sheet2

Cell Formulas
Range	Formula
D2:F7	D2	=AGGREGATE(15,6,$A$2:$A$6/($A$2:$A$6>$C2),COLUMNS($D$2:D$2))
G2:I7	G2	=AGGREGATE(14,6,$A$2:$A$6/($A$2:$A$6<$C2),COLUMNS($D$2:D$2))

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1	Raw		WIP
2	1		1	3	7	15	n/a	n/a	n/a
3	3		2	3	7	15	1	n/a	n/a
4	7		6	7	15	20	3	1	n/a
5	15		14	15	20	n/a	7	3	1
6	20		18	20	n/a	n/a	15	7	3
7			25	n/a	n/a	n/a	20	15	7
List

Cell Formulas
Range	Formula
D2:F7	D2	=IFERROR(INDEX(FILTER($A$2:$A$6,$A$2:$A$6>C2),{1,2,3}),"n/a")
G2:I7	G2	=IFERROR(INDEX(FILTER(SORT($A$2:$A$6,,-1),SORT($A$2:$A$6,,-1)<C2),{1,2,3}),"n/a")
Dynamic array formulas.

 

[ellison]

That's absolutely WONDERFUL, huge thanks indeed!!!!!!

 

[ellison]

That's absolutely WONDERFUL, huge thanks indeed!!!!!!

Pleased to say that BOTH of those methods work beautifully!!!

 

[Fluff]

Glad we could help & thanks for the feedback.