I have a data set where I need to find the closest 3 results of a given number.
column a
speed
47
36
12
6
1
column b
group
1
2
2
2
1
Given number "25" in c5
I want to be able to have the closest numbers to "25" in each group
I can get the closest to it using =INDEX($A$1:$B$5,MATCH(MIN(ABS($A$1:$B$5-$C$5)),ABS($A$1:$B$5-$C$5),0)) for the first closest number, and then
=MAX(IF($a$1:$a$5<$c$5,$b$1:$b$5)) and the min version as well for the 2nd 2 returns. However I can get the formula to only give the results for each group.
How can I put the extra criteria on those formulas to account for the group.
Thoughts?
column a
speed
47
36
12
6
1
column b
group
1
2
2
2
1
Given number "25" in c5
I want to be able to have the closest numbers to "25" in each group
I can get the closest to it using =INDEX($A$1:$B$5,MATCH(MIN(ABS($A$1:$B$5-$C$5)),ABS($A$1:$B$5-$C$5),0)) for the first closest number, and then
=MAX(IF($a$1:$a$5<$c$5,$b$1:$b$5)) and the min version as well for the 2nd 2 returns. However I can get the formula to only give the results for each group.
How can I put the extra criteria on those formulas to account for the group.
Thoughts?
