Extract Data from 2 columns if it matches condition

Question

This is how my main data sheet looks like

And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet. And then transpose them.

Partial answer to my question:

=INDEX(A2:B17,MATCH(G9&"*-*",A2:A17,))

However this only brings up 1 row, not all matches.

Using that with FILTER, same result:

=FILTER(A2:B17,A2:A17=INDEX(A2:A17,MATCH("M01"&"*-*",A2:A17,)))

Works but now I have no clue how to drag this down. To find other values.

Here is my sample sheet:https://docs.google.com/spreadsheets/d/1cAGa-Mc6KQjrTjwe8OM58wHbABDxI4toEbYOIs8_9VE/edit?usp=sharing

Answer 1

Finally found the answer to this:

=TRANSPOSE(FILTER(A2:B17,ISNUMBER(SEARCH(G9&"* - *",A2:A17))))

Answer 2

Finally found the answer to this:

=TRANSPOSE(FILTER(A2:B17,ISNUMBER(SEARCH(G9&"* - *",A2:A17))))

In addition to your approach using FILTER, ISNUMBER, and SEARCH, there are additional ways to achieve this including:

FILTER w/ REGEXMATCH

=TRANSPOSE(FILTER(A:B,REGEXMATCH(A:A, G9&" -")))

SORTN w/ REGEXMATCH

=TRANSPOSE(SORTN(A:B, 1,1, REGEXMATCH(A:A, G9&" -"),0))

QUERY

=TRANSPOSE(QUERY(A2:B, "Where A Matches '"&G9&" -.*'"))

Approach	Character Count
=TRANSPOSE(FILTER(A2:B17,ISNUMBER(SEARCH(G9&"* - *",A2:A17)))) * copied formula	61
=TRANSPOSE(FILTER(A:B,REGEXMATCH(A:A,G9&" -")))	47
=TRANSPOSE(SORTN(A:B,1,1,REGEXMATCH(A:A,G9&" -"),))	51
=TRANSPOSE(QUERY(A2:B,"Where A Matches '"&G9&" -.*'"))	54

FILTER  ISNUMBER  QUERY  REGEXMATCH  SEARCH  SORTN  TRANSPOSE