Search code examples
matchgoogle-sheets-formulacountingcountifpartial-matches

How do I search for a value in a different sheet, possibly within a string of text

I have values I need to search for within another sheet (4 columns), the fourth column's cells containing strings of text. I need a formula that is able to search for and count how many times the value shows up in the other sheet, even within a string of text. Preferably I'd like it find the exact match of the text. Sometimes a cell will be 'abcd' and it will count 'abcd1.'

=COUNTIF(Sheet2!A:D, A30)

So far this only gets me the amount of times the value shows up when on its own in a cell, but not within the string of text. I believe there will also be some concatenating involved for some of the values. Suggestions?

Solution

  • try:

    =COUNTIF(Sheet2!A:D, "*"&A30&"*")

    or:

    =INDEX(SUM(IFERROR(REGEXMATCH(Sheet2!A:D&"", ".*"&A30&".*")*1, 0)))