Forum Discussion
Filtering an Array based on a list of Criteria
I have an array that tracks every project that every project manager, project engineer, and superintendent is on and I am trying to set up a function to search through a single list of names and return just their projects. The PM, PE, and SUP on the array all have their own columns since there can be 1 of each on a single project. and the list im pulling from is within a single separate column pulling on the names I select. I have had success pulling from one column at a time using a FILTER(ISNUMBER(MATCH)) function but I cant get it to search and return all values from all three columns. Any thought on how to expand the search?
Thank you!
You just need to ADD the conditions together:
=FILTER([projectNames], ([name]=[PM])+([name]=[PE]) + ([name]=[SUP]) )
I notice you said you used ISNUMBER(MATCH)) but not sure why or how you have that setup. I could see ISNUMBER(SEARCH(name, PM)) if that field could contain multiple names (e.g. a list of names) and the name you are searching for may be only 1 in the field.
hope that helps you figure it out but if not maybe more info and a copy of the sheet would hel
3 Replies
You may try COUNTIFS() with FILTER().
=FILTER(A2:B16,COUNTIFS(H2:H4,B2:B16))You just need to ADD the conditions together:
=FILTER([projectNames], ([name]=[PM])+([name]=[PE]) + ([name]=[SUP]) )
I notice you said you used ISNUMBER(MATCH)) but not sure why or how you have that setup. I could see ISNUMBER(SEARCH(name, PM)) if that field could contain multiple names (e.g. a list of names) and the name you are searching for may be only 1 in the field.
hope that helps you figure it out but if not maybe more info and a copy of the sheet would hel
Thank you for your responses, what I ended up getting to work was the following. (Though probably quite inefficient)
IFERROR(SORT(FILTER('Name Projections'!$A$3:$AI$54,ISNUMBER(MATCH('Name Projections'!$C$3:$C$54,Utilization!$H$74:$H$88,0))+ISNUMBER(MATCH('Name Projections'!$D$3:$D$54,Utilization!$H$74:$H$88,0))+ISNUMBER(MATCH('Name Projections'!$E$3:$E$54,Utilization!$H$74:$H$88,0))+ISNUMBER(MATCH('Name Projections'!$F$3:$F$54,Utilization!$H$74:$H$88,0)),"Make Selections"),B4,1),"Needs Selection and/or Sorting Input")
Where the following columns related to specific names that I could select individually with check boxes on another tab would pull their projects and schedules for comparison.
C Column: PMs
D Column: PEs
E Column: Sups
F Column: Sup 2nd
I cant provide the rest of the sheet as it contains current actual projects and information.
Thank you again!
