Forum Discussion
element of a column equal any element of a list
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
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)
Can't say I understood your question(s). As for the formula in the latest post, COUNTIFS() requires the reference, not the array returned by FILTER(). Close to your formula could be
=COUNTIFS($D$5:$S$5, "", $D$6:$S$6, "<>") > 0