Search code examples
google-sheetsfiltergoogle-sheets-formulaarray-formulasgoogle-sheets-query

Filter Cells that matches partially

I have data in cell C50:C59 like this

1.A Level Redspot P1 Topical-2Pcs
2.A Level Redspot M1 Topical-2 Pcs
3.O LEVEL BIOLOGY REVISION GUIDE (CAMBRIDGE)- 4 PCS
4.A LEVEL REDSPOT YEARLY ALL SUBJECT (2 SET)
5.O LEVEL CHEMISTRY REVISION GUIDE (CAMBRIDGE)- 2 PCS
6.O LEVEL PHYSICS REVISION GUIDE (CAMBRIDGE)- 2 PCS
7.O LEVEL CAMBRIDGE WORKBOOK 1 SET

For example, when I type in cell B49, "a red" I want it to filter out cells containing these text partially

1.A Level Redspot P1 Topical-2Pcs
2.A Level Redspot M1 Topical-2 Pcs
4.A LEVEL REDSPOT YEARLY ALL SUBJECT (2 SET)

tried using filter and search, C50:C59 contains the data, B49 is my search box but it requires exact text in cell B49 to filter out, I want it to search and filter out if it matches any cell partially

=filter(C50:C59,search(B49,C50:C59))

Please give me some hint, it will save a lot of time.

Solution

  • you can use a simple query to for this task:

    =ARRAYFORMULA(QUERY(LOWER(C50:C56), "where Col1 contains '"&B49&"'"))

    0

    for total casing try:

    =ARRAYFORMULA(VLOOKUP(QUERY(LOWER(C50:C56), 
 "where Col1 contains'"&B49&"'"), C50:C56, 1, 0))

    0