Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find multiple text in diff cells in another worksheet? | Excel Worksheet Functions | |||
Find duplicates in data with multiple criteria | Excel Discussion (Misc queries) | |||
SUM with multiple numeric and text criteria | Excel Discussion (Misc queries) | |||
find minimum of range based on multiple criteria | Excel Worksheet Functions | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions |