Forum Discussion
FILTER Function - "include" parameter as string from another cell
paejunk Here is another alternative:
For the set of options enter them in a cell like this:
={ option1, option2, option3, ....}then use a formula like this:
=FILTER($G$1#,MMULT(--($G$1#=$I$1#),SEQUENCE(COLUMNS($I$1#),,1,0)))where G1# defines the conditional range and I1 is where the options are (in format noted above)
EDIT: I noticed your recent comment you are trying to understand all the options and I noticed I didn't do a good job equating this formula to your situation. I notice you use structured references for a table so based on the formula:
=FILTER(Table1,(Table1[CODE]="T1") + (Table1[CODE]="P1"))
let me update the above so in cell A1 would be the formula:
={"T1", "P1"}
this is nothing more than an array of constants which will 'spill' across columns. Alternatively you could use semicolons to 'spill' down rows but the formula would need a TRANSPOSE or you can use simple Text and use the formula TEXTSPLIT to create the array. In anycase, the point is to be flexible for more or less options being entered.
The formula would then be put in row 2 to leave row 1 for the spill from A1:
=FILTER(Table1,MMULT(--(Table1[CODE]=$A$1#),SEQUENCE(COLUMNS($A$1#),,1,0)))
alternatively if you use Text in A1 I would use a simple comma deliminated list like:
T1, P1
then the whole list is in that 1 cell A1 but the following formula requires text manipulation like so:
=FILTER(Table1,MMULT(--(Table1[CODE]=TRIM(TEXTSPLIT($A$1,",")) ),SEQUENCE(COLUMNS(TEXTSPLIT($A$1,",")),,1,0)))
And finally since you genuinely seem interested in understanding these options here is a quick explanation:
FILTER() will filter an input or rows (or columns) based on a criteria. That criteria must be a column (or row) with the same number of elements as the input and will be checked as TRUE or FALSE for each. note any value other than zero is considered TRUE. So in the above I compare the column of CODE with a row of acceptable options to create a table of 0s and 1s and then use MMULT to convert that table into a single column were each row is essentially OR'd together. I use MMULT instead of OR because OR would OR the ENTIRE table of 0s and 1s and not do each ROW individually.
I hope that helps.