Hi Everybody,
I'm trying to return values next to the smallest value in a list. I've tried searching but it's very difficult to find the right description to match what I'm doing.
In the below example I have a list of names with a number next to them. I want to return the names of people with the four lowest numbers which I have done using:
then
and so on.
However there are some names that have duplicate numbers which causes the formula to return the same name twice, rather than the duplicates in turn.
Can anybody help with how to construct a formula that would show the duplicates in order rather than miss one?
Many thanks,
Mark,
I'm trying to return values next to the smallest value in a list. I've tried searching but it's very difficult to find the right description to match what I'm doing.
In the below example I have a list of names with a number next to them. I want to return the names of people with the four lowest numbers which I have done using:
Excel Formula:
=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,1),$B$2:$B$11,0))
Excel Formula:
=INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,2),$B$2:$B$11,0))
However there are some names that have duplicate numbers which causes the formula to return the same name twice, rather than the duplicates in turn.
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Name | Count | Person 1 | Person 2 | Person 3 | Person 4 | |||
2 | Frank | 205 | Carl | Frank | Frank | Mickey | |||
3 | Karen | 258 | |||||||
4 | Jamie | 256 | |||||||
5 | Lillian | 308 | |||||||
6 | Ian | 279 | |||||||
7 | Kelly | 205 | |||||||
8 | Carl | 203 | |||||||
9 | Clem | 297 | |||||||
10 | Mickey | 235 | |||||||
11 | Ruby | 246 | |||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2 | D2 | =INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,1),$B$2:$B$11,0)) |
E2 | E2 | =INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,2),$B$2:$B$11,0)) |
F2 | F2 | =INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,3),$B$2:$B$11,0)) |
G2 | G2 | =INDEX($A$2:$A$11,MATCH(SMALL($B$2:$B$11,4),$B$2:$B$11,0)) |
Can anybody help with how to construct a formula that would show the duplicates in order rather than miss one?
Many thanks,
Mark,