Quote:
Originally Posted by Claus Busch
Hi,
Am Wed, 14 Aug 2013 01:41:39 +0100 schrieb sporenta:
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)}
try:
=INDEX($A$1:$A$150,SMALL(IF($B$1:$B$150=4,ROW($1: $150)),ROW(A1)))
and enter the array formula with CTRL+Shift+Enter
Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
|
Claus,
That works for finding values greater than 4, and when I change it to find values less than 3, it works too. It's finding values in-between 3 and 4 that I still can't get to work. So, working off of your formula I tried:
=INDEX($A$1:$A$150,SMALL(IF(AND($B$1:$B$1503,$B$1 :$B$150<4),ROW($1:$150)),ROW(A1)))
CNTRL+Shft+Enter
But I still get a #NUM error.
Garry, that would be a easy way to go! I'm crunching lots of numbers that are often updated, and this is just the first stop on a longer process. It's easier to have other parts of the workbook look at presorted columns.