Forum Discussion
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 arguments contained in that cell as a string?
For a simplified example if I have =FILTER(C3:E12,D3:D12="Finance")
What I want is the D3:D12="Finance" string in a separate cell that is referenced by the filter function for example G22, such that G22 would contain D3:D12="Finance"
What I do not want is something like =FILTER(C3:E12,D3:D12=G22) and then G22 contains "Finance"
My reasoning for this is that I will have multiple boolean operators in the filter criteria and the number of arguments and there order will change based on another drop down menu that is tied to a VLOOKUP.
I have tried using the INDIRECT function which would look like this =FILTER(C3:E12,INDIRECT(G22)) and get a #REF! error
A simple =FILTER(C3:E12,G22) gives a #VALUE! error.
How do you get this function to store the boolean operations as a string in another cell?
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,TRUEOut 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'.
3 Replies
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,TRUEOut 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'.
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.
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.
