Formula to create a list of people who qualify

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I am trying to create a list of employees who meet a criteria and would like
the names to be added to another list in a new excel sheet.

Employee name is in cell A2:A100
Criteria is in cell d2:d100

criteria is less than -3%

Thanks!
 
Some thoughts:

=IF(ISERROR(SMALL(IF(Under3=$A$1,ROW('Sheet1!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1,""),ROW(A1))),"",INDEX('Sheet1'!$A$2:$A$100,SMALL(IF(Under3=$A$1,ROW('Sheet1'!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1,""),ROW(A1))))

The error trap can be written like this which is much more efficient and a
lot shorter:

=IF(ROWS(A$2:A2)<=COUNTIF(Under3,$A$1),INDEX(...),"")

Instead of calculating an array of offsets:

ROW('Sheet1'!$A$2:$A$100)-MIN(ROW('Sheet1'!$A$2:$A$100))+1

You can calculate a single offset like this:

SMALL(IF(Under3=$A$1,ROW($A$2:$A$100)),ROW(A1))-MIN(ROW($A$2:$A$100))+1

Using ROWS(A$1:A1) is more robust than using ROW(A1). ROW(A1) is more
vulnerable to row insertions which could "break" the formula. For example,
if you insert a new row 1 ROWS(A$1:A1) becomes ROWS(A$2:A2) and still
evaluates to 1 but ROW(A1) becomes ROW(A2) which evaluates to 2 and now
you'll miss the first instance of the criteria.
 
Back
Top