MATCH/SEARCH Function with multiple condition-CodePudding

I'am trying to filter a Data which is the same as this scenario

=FILTER(D:D,
ISNUMBER(SEARCH("TRUE",C:C))*
ISNUMBER(SEARCH("2022",B:B))*
ISNUMBER(MATCH(OR(6,8),A:A,0)))

"ISNUMBER(MATCH(OR(6,8),A:A,0)))" --- This is where my problem occurs, I'd like the match/search to be either of multiple choices.

output should be the highlighted data on the image

enter image description here

CodePudding user response：

=FILTER(D:D,ISNUMBER(SEARCH("TRUE",C:C))*IF(A:A=6,TRUE,IF(A:A=7,TRUE,A:A=8)))

This is the solution i have found so far Tho I am not sure if this is the best answer

CodePudding user response：

Although this might look a bit overcomplicated - I think it is easier to understand the formula when you look at it in 2 months.

Furthermore I always recommend to externalize parameters. Next year you want to filter for 2023 - or maybe your boss tells you tomorrow to filter column A by 9 as well ...

=LET(data,A1:D3,
isColumnAOK,BYROW(INDEX(data,,1),LAMBDA(z,ISNUMBER(MATCH(z,checkColumnA,0)))),
isColumnBOK,ISNUMBER(FIND(checkColumnB,INDEX(data,,2))),
isColumnCOK,ISNUMBER(FIND("TRUE",INDEX(data,,3))),
FILTER(data,isColumnAOK*isColumnBOK*isColumnCOK))

The LET-formula has one row per each condition - you should name them according to your business case.

Parameters are named ranges (F2:F3 and H2)