Forum Discussion
Solved
element of a column equal any element of a list
Hello, I am using a multi-parameter filter function and for one of the "include" parameter, I want to select any column that contain one of any elements of a pre-defined list. The problem is, the lis...
Not exactly. I'm not sure on what the user ticks and what is generated as result. The key is generated list is the reference, i.e. part of the grid, or an array generated by the formulae. If the latest that could be like
=LET(source, TOCOL(C:C,3), targets, {"c111";"c151"}, NOT( ISNA(XMATCH( source, targets))) )for the
Not the most inspiring of use cases, but is this the kind of thing you are trying to do?
= LET(
criterion, BYCOL(Table1, LAMBDA(field, OR(COUNTIFS(list, field)))),
FILTER(Table1[#All], criterion, TRUE)
)
Kind of - but somewhat simpler -
If my list was {"horse","cow"}, I would like to apply it to field4 and have it display 11 FALSE values then TRUE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE no matter if you filled field4 with a bunch of other non-horse and cow values (I hope this made sense)
If my list was {"horse","cow"}, I would like to apply it to field4 and have it display 11 FALSE values then TRUE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE no matter if you filled field4 with a bunch of other non-horse and cow values (I hope this made sense)
Something like that is part of the formula I provided.
= COUNTIFS(list, Table1[Field4]) > 0returns the Boolean string you specified for [Field4].
I tried that but the formula returns an error:
The picture below is a screenshot of the formula + error message. The list if defined by the nested function FILTER($D$5:$S$5;IF(ISBLANK($D$6:$S$6);FALSE;TRUE);"0") and basically adds elements of line 5 to the list when the users put an x in line 6 below each element
- ($D$5:$S$5 as the second argument for countif is just to check if it works. I would use the actual much longer dataset in real life)