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.

 

[jasonb75]

Your explanation of that part was clear, where I didn't have a lot of time to test it before posting I didn't see that it wasn't doing what I thought it would.

I think that I might have found a way to make it work as you asked a few posts ago, in that the first instance of the criteria is ignored. i.e. criteria 80 from 70,80,60,80,90 would return 70,80,90 while 60,70,80,90,100 would return 60,70,90,100.

As with the previous formula, it looked good to start with, but further testing revealed problems. If I can't get it working before I have to leave for the day, I'll post the formula anyway, maybe Bo or Dante will be able to see what I'm missing.

 

[jasonb75]

I think that this might do it. In L3, array confirmed and filled down (I went down as far as L20 for testing).

=IF(ISERROR(SMALL(IF(ABS($O$3:$O$30-$L$2)<=SMALL(ABS($O$3:$O$30-$L$2),4),IF((ROW($O$3:$O$30)-ROW($O$3)+1)<>MATCH($L$2,$O$3:$O$30,0),ROW($O$3:$O$30))),ROWS(L$2:L2))),"",INDEX($O$1:$O$30,SMALL(IF(ABS($O$3:$O$30-$L$2)<=SMALL(ABS($O$3:$O$30-$L$2),4),IF((ROW($O$3:$O$30)-ROW($O$3)+1)<>MATCH($L$2,$O$3:$O$30,0),ROW($O$3:$O$30))),ROWS(L$2:L2))))

Testing it with criteria 80 from 10,20,...270,280 the results are correct (60,70,90,100), with random values it also looks good.

In addition to filling the formula above down to L20, my testing method was to enter

=RANDBETWEEN(1,50) into O3 and fill down to O30 (change 50 to a lower number to increase probability of duplicates).
=INDEX(O:O,RANDBETWEEN(3,30) into L2
=COUNTIF($O$3:$O$30,L2) entered into M2 and filled down to M20 to verify that each result is returned the correct number of times.

I ran the compatibility check and it appears that the formula should be ok to use with excel 2003.

 

[Pejayuk]

Hi Jason,

Many thanks, you are a genius! I've been trying the last formula you did, and it is one hundred percent perfect. Thanks also for sharing the way to test it, that helped a lot. I've also tested it using my various sample lists and it's spot on every time, including with any duplicates/ties.

I realize you must have spent a fair bit of time helping me with this over the last couple of weeks, for which I am very, very grateful.

Hope you have a nice Christmas!