A sample file is almost always better, saves us from reconstructing your data, but I felt like some typing practice, so...
|
Q |
R |
S |
1 |
Name |
% |
Qty |
2 |
aa |
|
90 |
3 |
bb |
|
119 |
4 |
cc |
|
45 |
5 |
dd |
|
60 |
6 |
ee |
|
21 |
7 |
ff |
|
5 |
8 |
gg |
|
34 |
9 |
hh |
|
59 |
10 |
|
|
|
11 |
|
|
|
12 |
Tier 1 |
Tier 2 |
Tier 3 |
13 |
ee |
cc |
aa |
14 |
ff |
dd |
bb |
15 |
|
gg |
|
16 |
|
hh |
|
Q13=IFERROR(INDEX($Q$2:$Q$9,SMALL(IF($S$2:$S$9<=30,ROW($Q$2:$Q$9)-1),ROWS($A$1:A1))),"")
R13=IFERROR(INDEX($Q$2:$Q$9,SMALL(IF(($S$2:$S$9>30)*($S$2:$S$9<=60),ROW($Q$2:$Q$9)-1),ROWS($A$1:A1))),"")
S13=IFERROR(INDEX($Q$2:$Q$9,SMALL(IF($S$2:$S$9>60,ROW($Q$2:$Q$9)-1),ROWS($A$1:A1))),"")
these are all ARRAY formulas ...confirmed by pressing
CTRL+SHIFT+ENTER to activate the array,
not just ENTER. You will know the array is active when you see curly braces
{ } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
After you enter all 3, copy them down as needed
Bookmarks