Forum Discussion

Copper Contributor

Nov 17, 2023

Solved

Filter criteria portion of the FILTER function as a cell reference

The FILTER functions syntax is: FILTER(array,include, [if_empty]) I am wondering how to have the include portion (the filter criteria) of this reference a cell and have all of the boolean argume...

Formulas and Functions

PeterBartholomew1
Nov 17, 2023
Zack_Heidemann
If you really do mean a string in a separate cell, then you would need to convert it to an array before applying it.
= FILTER(list, TEXTSPLIT(commaSeparatedBooleanString,,",")) where 'commaSeparatedBooleanString' is TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE
Out of curiosity, why VLOOKUP? For me it is an obsolete function and, in so far as I have any feelings towards it, I would sum it up as 'good riddance to bad rubbish'.

PeterBartholomew1

Silver Contributor

Nov 17, 2023

Zack_Heidemann

If you really do mean a string in a separate cell, then you would need to convert it to an array before applying it.

= FILTER(list, TEXTSPLIT(commaSeparatedBooleanString,,","))

where 
'commaSeparatedBooleanString' is
TRUE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE

Out of curiosity, why VLOOKUP? For me it is an obsolete function and, in so far as I have any feelings towards it, I would sum it up as 'good riddance to bad rubbish'.

Zack_Heidemann
Copper Contributor
Nov 20, 2023
PeterBartholomew1

I supposed I could use a different function but the range it will be operating in will be quite small (something like 4 by 10), and it didn't seem like using INDEX(MATCH) was worth it if the range was going to be so small.
- PeterBartholomew1
  Silver Contributor
  Nov 20, 2023
  Zack_Heidemann
  My comment was merely an aside. It struck me as odd to be using FILTER but not to have switched to XLOOKUP, no more than that.