Multiple selection criteria

[Bigkahuna]

I have a worksheet with employee name, whether they are certified and their
function in separate columns. I want to use a formula to select only those
employees who are certified and perform the audit function and put the
results in a separate column. Is there a simple way to perform this
operation? I seem to remember seeing an article relative to this but can't
put my hands on it now.
Thanks

 

[Pimamedic]

What about trying Filter > Auto FIlter

 

[Bigkahuna]

No I want to have a column with the names of those who meet multiple
criteria. I actually need several columns with employees who meet difference
sets of criteria so auto filter won't provide what I need.

 

[T. Valko]

Try this array formula**. Note that this is slow to claculate if you have
1000's of rows of data.

Named ranges:

Name, refers to A2:A100
Status, refers to B2:B100

Enter a formula i a cell that returns the count of records that meet the
criteria.

E1: =COUNTIF(Status,"certified")

Extract the names where the status is certified.

Array entered** in E2:

=IF(ROWS(E$2:E2)<=E$1,INDEX(Name,SMALL(IF(Status="certified",ROW(Name)),ROWS(E$2:E2))-MIN(ROW(Name))+1),"")

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.