1

In Google Sheets, I have two columns of data.

The first column is labeled Category, and the second Contents

I want to search Contents for a text string and return Category wherever it is matched.

For example, in the table below, if I search Contents for the phrase amino acids I would expect the formula to return both Proteins and Urea cycle from Category:

Category Contents
Proteins amino acids, alpha-helix, beta-sheet, primary structure, secondary structure, tertiary structure
Urea cycle  transamination, amino acids, urea, glutamine, glutamate, liver, kidney
  • I've tried using the functions INDEX and MATCH but it only matched if the search term was the only text in the cell, as opposed to part of comma-separated values (CSV).

  • XLOOKUP failed for the same reason

  • I thought about splitting the CSV into an array using SPLIT but that creates a 2-dimensional array that MATCH doesn't support.

  • The closest I've gotten is the following formula but it returns the matching Contents instead of the Category:

      =FILTER(B:B, REGEXMATCH(B:B, "amino acids"))
    

Maybe an array formula is required.

How do I return multiple results from Category based on matching text substrings in Contents?

4
  • I have just added an answer to Google Sheets VLOOKUP with wildcards on range that will likely work for you.
    – Blindspots
    Apr 11 at 15:31
  • 1
    Both solutions in Google Sheets VLOOKUP with wildcards on range use vlookup which, according to my limited know-how, works only to the right. I can match something in column A and then return the value of A, B... but I cannot find in B and return A, can I?
    – SeanJ
    Apr 12 at 12:08
  • 1
    [Find a cell containing text in a range and return the text in that cell] (webapps.stackexchange.com/questions/169090/…) has 2 replies but none is marked as a solution. I tried the first and got an error. filter requires a single column or row. I honestly did not know what to make of the complex =BYROW....
    – SeanJ
    Apr 12 at 12:08
  • You are right this is a different problem. I have edited your question for clarity and submitted it for reopening. Please put your answer as a self-answer rather than an edit to the question. Thank you for responding in the comments and clarifying your problem.
    – Blindspots
    Apr 12 at 20:53

1 Answer 1

0

As you suspected you need an array formula to return multiple matches. There are many ways to achieve this.

You can wrap a non-array function in an array function such as ARRAYFORMULA or INDEX to return an array:

=INDEX(IF(REGEXMATCH(B:B, "amino acids"), A:A,))

=ARRAYFORMULA(IF(REGEXMATCH(B:B, "amino acids"), A:A,),))

You can use a native array function, like FILTER or QUERY, the latter including native support for regular expressions:

=FILTER(A:A, REGEXMATCH(B:B, "amino acids"))

=QUERY(A:B, "SELECT A WHERE B MATCHES '.*amino acids.*'")

You can use MAP or BYROW with a non-array function in a LAMBDA's formula to iterate through the rows, one by one:

=MAP(A:A, B:B, LAMBDA(a, b, IF(REGEXMATCH(b, "amino acids"), a,)))

=BYROW(A:B, LAMBDA(rw, IF(REGEXMATCH(INDEX(rw,,2), "amino acids"), INDEX(rw,,1),)))

Function Links

BYROW  FILTER  INDEX  LAMBDA  MATCH  REGEXMATCH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.