Find text in another worksheet when multiple criteria are met
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 |
Find text in another worksheet when multiple criteria are met
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. -- Biff Microsoft Excel MVP wrote in message ... 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 |
All times are GMT +1. The time now is 11:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com