filtering rows by keywords

nilanjenator If you have Excel for MS365, there are a number of different ways you can accomplish this. One option is to use the FILTER function to return the records that "contain" the selected keywords. The most common method is to use some variation of ISNUMBER with SEARCH or FIND; however, I prefer to use the following custom LAMBDA function (LIKE), which leverages the functionality of COUNTIF with BYROW:

LIKE:
=LAMBDA(range,criteria,[is_and_logic],
   BYROW(range, LAMBDA(row, LET(
      results, COUNTIF(row, criteria),
      IF(is_and_logic, PRODUCT(results), SUM(results))))))

Once created, the LIKE function can be used in the include parameter of the FILTER function. The generic syntax for this method is:

=FILTER(array, LIKE(range, criteria))

The criteria specified can be values entered into a cell or range and can include multiple criteria. Wildcard characters (*?~) can also be used to include records that begins with, ends with or contains the specified values. For example:

=FILTER(tblData, LIKE(tblData[Type], B1), "No records found")

The above formula will return all records from tblData where the values in column [Type] are equal to the value entered in cell B1, for example "dog". However, you can also input "*dog*" to return all records that contain "dog".

The TEXTSPLIT function can also be used to input multiple criteria in the same cell and return records that contain either value. For example:

=FILTER(tblData, LIKE(tblData[Type], "*"&TEXTSPLIT(B1, "; ")&"*"), "No records found")

In this example, the asterisks were concatenated to the criteria directly in the formula so it will always return records that contains the criteria without having to type the asterisks in the cell. For example, if "dog; cat" was entered in cell B1, it would return all records that contain either "dog" or "cat".

Having said that, you could also list all of the possible criteria in a table, then link a slicer to that table, which in turn can be "linked" to the LIKE function to return the applicable FILTER results. The best way to do this is to use Power Query to split the column containing the comma separated values into rows, then remove other columns and remove duplicates. Load the results to a table in the workbook, rename it "tblCriteria", then add a new column to the table called "Visible" with the following formula:

=SUBTOTAL(103, [@Type])

Insert a slicer for the "Type" column, then link the FILTER function to the visible criteria as follows:

=LET(
criteria, "*"&FILTER(tblCriteria[Type], tblCriteria[Visible])&"*",
FILTER(tblData, LIKE(tblData[Type], criteria), "No records found")
)

Note: as new data is entered into the source table (tblData), you will need to click the Refresh All button found on the Data tab of the ribbon to update the available keywords in the criteria table.

Please see the attached workbook, which contains all of the methods outlined above...