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.
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.