Using FILTER() to return a variable number of records

[MrShorty]

As I get used to this newer version of Excel, one of the tasks I frequently do is Filtering a batch of calculations. My spreadsheets look substantially like the attached. In the calculations tab, there will be identifiers, a few columns of inputs, several columns of intermediate calculations, and a few output columns with the final results. What I then need to do is choose a few columns (These don't change frequently, so I'm not worried about these being dynamic in the function), then filter based on id no. In my old version of Excel, I used lookups to bring all of the desired data over into "Table", then used Autofilter to filter on id no. I'm trying to figure out the same kind of thing using the newer FILTER() function.

You can see my current attempt in the sample. It works great as long as I am looking for exactly two id no. in A6 and A7. If I want to add another id no, then I need to add a +(calculations!A4:A22=Table!A8) to the function.

What I want to be able to do is enter the desired id nos in Table!A6:A10 (from just one id no to all five possible), and have those records show up in columns C:E.

How do I structure the "criteria" argument to handle this?

[Fluff13]

How about

Please Login or Register  to view this content.

Or for both D & E

Please Login or Register  to view this content.

[MrShorty]

Thank you Fluff13, that works.

[Fluff13]

You're welcome & thanks for the feedback.

[MrShorty]

The CHOOSECOLS() function inside there was a nice bonus. Now I can put the desired column numbers in C1:E1 (1,2,8), and use those as reference =FILTER(CHOOSECOLS(calculations!$B$4:$H$22,C1,D1,E1),ISNUMBER(XMATCH(calculations!$A$4:$A$22,FILTER(A6:A10,A6:A10<>"")))). Now when I want to see different columns in "Table", I can simply enter the desired columns in row 1.