filter using criteria from list/array

[boukasa]

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?

[#2]

@boukasa 

Find attachment

[#3]

@boukasa 

Do you consider Power Query or data model PivotTable as an options ? With them that's straightforward way to generate the result.

[#4]

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.

[#5]

@boukasa 

And here is with PivotTable

[#6]

@boukasa yes, Refresh All is required for such options.

[#7]

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.

[#8]

@boukasa 

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.

[#9]

Juliano-Petrukio, I would need to manually update the formula each time the array grows/shrinks, is that correct?

[#10]

@boukasa 

Without arrangements yes, but I prepared an example for you.

You can add new project name and new volunteer names as per sheets.

What I did was:

- There are different tables for each set of data (

1. tblProject (Containing the different project names)
2. tblVonlunteer (Containing the volunteer names)
3. MainTable (Project assigned to volunteers)

To get all the projects at once you just need to type 

=tblProject

So using the TEXTJOIN() formula, I can put everything in a single cell.

=TEXTJOIN(", ",TRUE,
     FILTER(MainTable[Name],MainTable[Project]=I3))

[#11 · Accepted solution]

@boukasa 

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]

Sergei, the construct "array = transpose(array)" to give me a match table is what I needed. It leads me to another question, which I will post. Thanks so much.

[#13]

@boukasa , glad it helped