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
=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
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)
I'm wondering whether the SEARCH function is necessary. If your columns always come in the given format, then you don't have to search for the values first but check for the condition right away. SEARCH would only be helpful here if you want to check for the presence of the criterium anywhere in a cell even when accompanied by other characters.
The following function is considerably shorter, but requires the columns to always have the same format:
=FILTER(D:D,((A:A=8)+(A:A=6))*IFERROR(VALUE(LEFT(B:B,4))=2022,1)*(C:C=TRUE))
In this solution the following format assumptions apply: