Forum Discussion

pasdinspi's avatar
pasdinspi
Copper Contributor
Jan 02, 2024
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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 03, 2024

    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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 03, 2024

I might as well include where I had go to covering as many bases as I could think of!

Adding new items to the table on the left adds to the options table using the formula

= SORT(UNIQUE(FILTER(list, list<>"")))

Conditional formatting exposes the check boxes.  The use can then select to subset of the options as being of relevance.  A filtered selection is achieved using

= FILTER(options, checkBox)

Conditional formatting is also used for the yellow highlighting.  

where 'options' is a dynamic range and 'checkBox' is on offset range based upon 'options'.  The Boolean array searches the original 'list' for values that appear in the selection

= COUNTIFS(select, list) > 0

The workbook gives something of the feeling of going round in circles but there you go!

 

 

Resources