Find text in another worksheet when multiple criteria are met

[mrhartwig]

Hello All. I am trying to pull employee's names from one worksheet
to
another but only if they are active and work in a particular
location.

Column A: Active or Inactive status
Column B: Location
Column G: EE Name


Here is what I have tried:


=Index('CPC Roster'!G2:G5,Match(1,('CPC Roster'!A2:A5="Active")*('CPC
Roster'!B2:B5="BURBANK"),0))


This sort of works except when it comes to a name that is not active
it repeats the name above until it reaches an active one.


I've also tried:


=LOOKUP(2,1/('CPC Roster'!A2:A10="Active")/('CPC Roster'!
B2:B10="BURBANK"),'CPC Roster'!G2:G10)


For some reason this skipped a handful of people in the first few
rows
and still repeated names.


What I really need it to do is only bring over the active employees
and skip the inactive employees. Does anyone have any suggestions?
Thanks so much.


--Michelle

 

[T. Valko]

The easiest way would be to autofilter on "active" and "location" then
simply copy/paste the results.

This could be done with an array formula but whether it's practical depends
on how many rows of data need to be searched and how many records will meet
these conditions. If it's 1000's of rows of data a formula solution would
not be practical.