Forum Discussion
filter using criteria from list/array
The basic question is, how can I utilize FILTER next to an array to produce filtered results for a list of items? FILTER does not appear to allow me to use an array reference in the criterion. Is there a workaround? (UPDATE: I mean, is there a workaround that produces maintenance-free spill-type results.)
Example - imagine I have an array like so:
Projects
A
B
C
In a separate part of the workbook I have a database of projects and eligible volunteers like so:
Volunteers
Project Code Eligible Volunteers
A Mary
A John
B John
B Sue
C Peg
C Mary
C John
I want to use the project code as the criterion for a filter, and show the filter results next to the code, like so:
Project Code Volunteers
A Mary John
B John Sue
C Peg Mary John
I expected to use something like this:
=TRANSPOSE(INDEX(FILTER(EligibleVolunteers, EligibleVolunteers[Project Code]=Projects),,2))
But when I use array Projects in the criteria field, I get #n/a
I can rewrite the formula to use a cell reference to the Project Code, and drag it down, and it works. But that makes the spreadsheet not automatic anymore.
Is there a workaround or different approach? Also, what's the rationale for not allowing a dynamic reference in the criteria parameter?
If return everything in one spill hat's with lambdas and new helper functions, something like
=LET( u, UNIQUE( MainTable[Project] ), CHOOSE( {1,2}, u, BYROW( IF( u = TRANSPOSE( MainTable[Project]), TRANSPOSE( MainTable[Name] ), ""), LAMBDA(r, TEXTJOIN(", ", 1, r) ) ) ) )
12 Replies
And here is with PivotTable
Do you consider Power Query or data model PivotTable as an options ? With them that's straightforward way to generate the result.
- I'm hoping for a solution that is "automatic" - this is a simplified example from a complex spreadsheet. With tables and arrays my sheet is always up to date as data is added. My understanding was that users would have to update the pivot tables each time new data is entered.
boukasa yes, Refresh All is required for such options.
- Thank you Juliano. I'm wanting to find an approach that maintains spill-type results, so that as the data is added, the calculated formulas automatically extend to the new data.
You can achieve that for sure.
As you didn't provide a sample data (structured excel file) I developed that example based on your post details.But as you can see, it is pretty simple to make the adjustments.
