Finding Nearest 3 Values Whether Higher Or Lower

[Pejayuk]

Hi, I would really appreciate help with finding a solution for the following.

I have a list of data contained in columns A-K. This data cannot be sorted and I need to find the 3 nearest values to the value I select from column F.

I've been trying to achieve this by using {=MIN(IF(O3:O30>L2,O3:O30))} and {=MAX(IF(O3:O30<L2,O3:O30))}. (I'm only using 30 rows in this test). If I repeat these formulas a few times, it does give roughly the correct results, but also some I don't want.

It doesn't work at all if I select the lowest or highest value. For example: If I test it with values 10 - 300 increasing by 10 per row. If I select 10, because there is no lower value, I would like it to find the three closest higher values. Likewise, if I select 300, I would like to display 290, 280, 270 in that order. Another problem is, the actual data in column F will have a minimal number of duplicates.

Thanks in advance for any suggestions.

 

[DanteAmor]

Try this Array formula:

Book1
	A					F						L	M
1						Values						Criteria	Results
2						1						5	4
3						4							6
4						6							3
5						8							7
6						3							2
7						5
8						2
9						7
10						5
Hoja1

Cell Formulas
Range	Formula
M2:M6	M2	{=INDEX($F$2:$F$10,MATCH(SMALL(IF($F$2:$F$10<>$L$2,ABS(($F$2:$F$10+(ROW($F$2:$F$10)/100))-$L$2)),ROWS($C$2:C2)),ABS(($F$2:$F$10+(ROW($F$2:$F$10)/100))-$L$2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Bo_Ry]

Please try at B2

Book1
	A	B
1		40
2	28	39
3	33	43
4	39	33
5	43	48
6	48	28
7	53
8	58
9	63
10	68
11	73
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX(A:A,MOD(SMALL(INDEX(ABS($A$2:$A$11-$B$1)*10^9+ROW($A$2:$A$11),),ROWS(B$2:B2)),100))

 

[jasonb75]

Nice try, Bo_Ry, but you missed one of the requirements.

If the criteria value appears in the list, it should not be included in the results.

Also, it appears that neither your suggestion, or Dante's include any kind of limiter to blank out values which don't meet the criteria of '3 closest' (note in my last suggestion that 3 values are tied for 3rd place).

 

[Bo_Ry]

Jason, I see.
How about this?

Book1
	A	B
1		40
2	40	43
3	55	43
4	44	44
5	43	45
6	43
7	45
8	58
9	63
10	1
11	1
12	1
13
14
15
16
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	{=IFERROR(SMALL(IF(($A$2:$A$18<>$B$1)*(ABS($A$2:$A$18-$B$1)<=AGGREGATE(15,6,ABS($A$2:$A$18-$B$1)/(FREQUENCY($A$2:$A$18,$A$2:$A$18)>0)/($A$2:$A$18<>$B$1),3)),$A$2:$A$18),ROWS(B$2:B2)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[jasonb75]

That looks good for when the OP upgrades their version of excel, Bo

I've discovered the version of Excel on there doesn't have the AGGREGATE or IFERROR functions.

Your formula is more concise and efficient than my attempt for newer versions of excel.
My last formula works with pre 2007 versions, however one thing that is still unclear (partially my fault for not asking the question) is the expected order of the results.

Testing with the data set that I used in post #9, your formula returns 81,95,95,97,97 where mine returns 97,97,95,95,81
In my opinion, the 'correct' result would be either 95,95,81,97,97 or 95,95,97,97,81 (95 is the closest value to the criteria of 89 so should be first). Although I think maybe it would be best to wait for some feedback from the OP before making any more changes to the formulas.

 

[Bo_Ry]

Thanks, Jason.
Without IFERROR, my formula would be very long.

 

[jasonb75]

Also, without AGGREGATE, your method might not be possible.

I had many attempts before finding one that works.

 

[Pejayuk]

Many thanks to all who have made suggestions. As Jason has said, the issue is needing to use this on an old version of Excel.

Jason, I have been testing your new formula and this is what I have found........

Using your sample list and target, I get the exact same 5 results. (97,97,95,95,81).
The only glitch seems to be when using a low number for the target.

Using my simple 10 -100 in increments of 10 sample. This is what I get.
Target number..............Result
1,2,3,4.............................0,0,0,0,0,0

5,6,7,8,9.........................10,0,0,0,0,0

10...................................20,0,0,0,0,0

11,12,13,14....................10,20,0,0,0,0

15...................................10,20,30,0,0,0

In this example, anything 15 and above seems to give perfect results. I don't get any error message, so I'm not sure if it is anything to do with the maximum allowable number of functions. I have also tested it on a 2016 version with the same results.

With regards to the expected order of results. Yes, the best outcome would be as you assumed, the nearest to the target first. Though I understand from what you said yesterday, that this won't be possible with the old Excel. The only time this would be an issue, is if more results were returned than I actually needed. (Which I know will happen in some circumstances, but it should still be fine).

Thanks again

 

[DanteAmor]

Using my simple 10 -100 in increments of 10 sample. This is what I get.
Target number..............Result
1,2,3,4.............................0,0,0,0,0,0
5,6,7,8,9.........................10,0,0,0,0,0
10...................................20,0,0,0,0,0
11,12,13,14....................10,20,0,0,0,0
15...................................10,20,30,0,0,0

Hi @Pejayuk, Did You try my formula from post #11?, it works with all your examples.

If you want 3 values, just copy the formula into 3 cells.
The following are examples of 6 nearby values, but if you only want 3, then put the formula in 3 cells.

Book1
	A					F						L	M
1						Values						Criteria	Results
2						10						1	10
3						20							20
4						30							30
5						40							40
6						50							50
7						60							60
8						70
9						80
10						90
11						100
Sheet

Cell Formulas
Range	Formula
M2:M7	M2	{=INDEX($F$2:$F$11,MATCH(SMALL(IF($F$2:$F$11<>$L$2,ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2)),ROWS($C$2:C2)),ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book1
	A					F						L	M
1						Values						Criteria	Results
2						10						4	10
3						20							20
4						30							30
5						40							40
6						50							50
7						60							60
8						70
9						80
10						90
11						100
Sheet

Cell Formulas
Range	Formula
M2:M7	M2	{=INDEX($F$2:$F$11,MATCH(SMALL(IF($F$2:$F$11<>$L$2,ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2)),ROWS($C$2:C2)),ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book1
	A					F						L	M
1						Values						Criteria	Results
2						10						5	10
3						20							20
4						30							30
5						40							40
6						50							50
7						60							60
8						70
9						80
10						90
11						100
Sheet

Cell Formulas
Range	Formula
M2:M7	M2	{=INDEX($F$2:$F$11,MATCH(SMALL(IF($F$2:$F$11<>$L$2,ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2)),ROWS($C$2:C2)),ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book1
	A					F						L	M
1						Values						Criteria	Results
2						10						10	20
3						20							30
4						30							40
5						40							50
6						50							60
7						60							70
8						70
9						80
10						90
11						100
Sheet

Cell Formulas
Range	Formula
M2:M7	M2	{=INDEX($F$2:$F$11,MATCH(SMALL(IF($F$2:$F$11<>$L$2,ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2)),ROWS($C$2:C2)),ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book1
	A					F						L	M
1						Values						Criteria	Results
2						10						15	10
3						20							20
4						30							30
5						40							40
6						50							50
7						60							60
8						70
9						80
10						90
11						100
Sheet

Cell Formulas
Range	Formula
M2:M7	M2	{=INDEX($F$2:$F$11,MATCH(SMALL(IF($F$2:$F$11<>$L$2,ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2)),ROWS($C$2:C2)),ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Book1
	A					F						L	M
1						Values						Criteria	Results
2						10						20	10
3						20							30
4						30							40
5						40							50
6						50							60
7						60							70
8						70
9						80
10						90
11						100
Sheet

Cell Formulas
Range	Formula
M2:M7	M2	{=INDEX($F$2:$F$11,MATCH(SMALL(IF($F$2:$F$11<>$L$2,ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2)),ROWS($C$2:C2)),ABS(($F$2:$F$11+(ROW($F$2:$F$11)/1000))-$L$2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Bo_Ry]

Book1
	K	L	M	N	O
1		Errror when more than 3	No Error	No Limit
2	Criteria	10			List
3	Results	20	20	20	10
4		20	20	20	20
5		30	30	30	30
6		30	30	30	40
7		#NUM!		40	50
8		#NUM!		40	60
9		#NUM!		50	70
10		#NUM!		50	80
11		#NUM!		60	90
12		#NUM!		60	100
13					10
14					20
15					30
16					40
17					50
18					60
19					70
20					80
21					90
22					100
23
24
25
26
27
28
29
Sheet2

Cell Formulas
Range	Formula
L3:L12	L3	{=MOD(SMALL(IF(($O$3:$O$30<>"")*(ABS($O$3:$O$30-$L$2)<SMALL(IF($O$3:$O$30<>"",IF(FREQUENCY($O$3:$O$30,$O$3:$O$30),ABS($O$3:$O$30-$L$2))),4))*($O$3:$O$30<>$L$2),ABS($O$3:$O$30-$L$2)*10^9+$O$3:$O$30),ROWS(L$3:L3)),10^6)}
M3:M12	M3	{=IF(ROWS(M$3:M3)>SUM(($O$3:$O$30<>"")*(ABS($O$3:$O$30-$L$2)<SMALL(IF($O$3:$O$30<>"",IF(FREQUENCY($O$3:$O$30,$O$3:$O$30),ABS($O$3:$O$30-$L$2))),4))*($O$3:$O$30<>$L$2)),"",MOD(SMALL(IF($O$3:$O$30*(ABS($O$3:$O$30-$L$2)<SMALL(IF($O$3:$O$30<>"",IF(FREQUENCY($O$3:$O$30,$O$3:$O$30),ABS($O$3:$O$30-$L$2))),4))*($O$3:$O$30<>$L$2),ABS($O$3:$O$30-$L$2)*10^9+$O$3:$O$30),ROWS(M$3:M3)),10^6))}
N3:N12	N3	{=MOD(SMALL(IF(($O$3:$O$30<>"")*($O$3:$O$30<>$L$2),ABS($O$3:$O$30-$L$2)*10^9+$O$3:$O$30),ROWS(N$3:N3)),10^6)}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.