Google Sheets - Create a list - Search multiple columns or cell ranges (like the filter formula), return multiple rows as a result

[WillHolman]

I am a rookie at this and I spent hours trying to figure this out but can find anything that really helps.

When I use the filter formula it only lets me search one keyword in one column.
I want to be able to search for multiple keywords across multiple columns.

If would be cool if there was a formula that says: Search "this range of cells" Look for "this key word" or "this keyword" and list "these rows"

Scenario 1 Looking at the photo. I want to search columns C through M for a selected color(s) In this case Red. Return all the rows (Column A and B only) that have red in one of the row's columns. Result should be:
Tim | Tim@
Bob | Bob@ etc

Scenario 2 Looking at the photo. I want to search columns C through M for a selected color(s) In this case Red and Blue Return all the rows that have red or blue in one of the row's columns. Result should be all the names and emails.
I will input this on a seperate sheet and be able to add new values if I add new rows on the main sheet.

 

[Fluff]

Hi & welcome to MrExcel.
How about

FILTER(A2:B10,MMULT(--(C2:M10="Red"),SEQUENCE(columns(C2:M10))^0)>0)

 

[WillHolman]

Hi & welcome to MrExcel.
How about

FILTER(A2:B10,MMULT(--(C2:M10="Red"),SEQUENCE(columns(C2:M10))^0)>0)

I been posting this question everywhere, this is my first response.
It works amazing.

where is has (C2:M10="Red") can I add or (C2:M10="Blue")

 

[WillHolman]

Never mind I was able to figure it out.

(C2:M10="Red")+(C2:M10="Blue")

I been trying to figure this out all week.
This made my day.

Is there anywhere to donate to the site?

 

[Fluff]

You're welcome & thanks for the feedback.

Is there anywhere to donate to the site?

Nope, no need.

 

[WillHolman]

@Fluff

On the formula I am using Cells in place of the "Red" and "Blue"
Sometimes one of those are left blank so the formula pulls all the results where there is a blank value.

Can I add a statement to ignore blanks?

 

[Fluff]

You could use

=FILTER(A2:B10,MMULT((C2:M10=IF(N2="",1,N2))+(C2:M10=IF(N3="",1,N3)),SEQUENCE(COLUMNS(C2:M10))^0)>0)