I have a big challenge filtering data where include array "colors_toFind" (it's a range A2:A11) where a column "tblCars[Color]" in the table "tblCars" contains values separated by space&comma. I'm using the following function:
=FILTER(tblCars,ISNUMBER(X.MATCH(tblCars[Color],colors_toFind,0)))
However, it doesn’t return any results or only those rows where there are no additional values separated by a comma.
Please find attached prntscr or https://drive.google.com/drive/folders/1CPAqcKpP0oE83hOeHxyZ19rSWviwxtq-?usp=sharing
Any advice would be appreciated.
Cheers
I've changed your set-up a bit. You'll note that you can't use XMATCH (or MATCH) and an exact match if your color column has multiple colors. You'll need SEARCH for the colors within the colors. Therefore, I've added a column (may be hidden) that determines row-by-row if the car color is one of the selected ones. And, you can't use blank cells in the search list.
Note that I also made the SEQUENCE formula more dynamic by using the #-operator.
See if this will work for you (attached).
@riny
Hello,
Your solution works perfect. I wonder if there is any way to avoid adding an additional column to the data table (I mean match column). The file uploaded here is just an example—I’m working on a some project where my master data table is the result of a Power Query query. I know Excel can handle this, but I’d like to avoid expanding the table.
Anyway, if not, it is still really helpful to me.
Normally, I wouldn't mind to work with helper columns. Just to see matters clear and manageable. But, of course, nowadays we have many new functions at our disposal. You may remove the helper column and use this formula in C35. What it does is create the 'helper' in an array called "matched" and then filter the larger table for matched rows.
=LET( matched, BYROW(tblCars[Color],LAMBDA(r,SUM(IFERROR(SEARCH(tblColor[Color],r),0)))), FILTER(tblCars,matched) )
