I have a list of names in column A and a list of scores (between 1 and
5) in column B. I want to sort the names out into three categories:
scores greater than 4, scores between 3 and 4, and scores less than 3,
so a total of 3 formulas. The following formulas copied down their
columns return all names with the appropriate scores:

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150=4,ROW($B $1:$B$150)),ROW(1:1)),1)}

and

={INDEX($A$1:$B$150,SMALL(IF($B$1:$B$150<3,ROW($B$ 1:$B$150)),ROW(1:1)),1)}

...but I can't make a formula work that returns values between 3 and 4.

Any ideas?

Try specifying...

AND(<4,3)

...as your criteria.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Not sure if I have the syntax correct, but I get #NUM when I try the following:

={INDEX($A$1:$B$150,SMALL(IF(AND($B$1:$B$150<4,$B$ 1:$B$1503),ROW($B$1:$B$150)),ROW(1:1)),1)}

Just curious why you don't just sort the range on Col B?

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion