MATCH STRING IN A COLUMN AND RETURN ALL ROWS (entire row) THAT MATCH THE STRING

[Ratnesh]

Dear Team Members,

Hope you are all doing well.


I need some help with regards to my excel file. (Sample file attached)

I have an array containing data into rows and columns.
I want to search for a string in one of the columns (Lets say the first column)
For eg : In the attached file, I have shown the search text as "red"

I want the formula to search in the entire column for the string "red" and return the entire row as outputs.

So If "red" appears 5 times .. it should return all those 5 rows where the text "red" appears.

I have shown the desired output as well in my excel file.

Please help !!

 

[Joe4]

Welcome to the Board!

Have you thought of using Filters (to filter in place) or Advanced Filters (to filter to a new location)?

Filters are pretty straightforward. Just select the whole range, and then click the Filter button.
Then go to the drop down on your Items field, and choose Text Filters -> Contains -> red

For Advanced Filters (to filter to a new location), see: Excel Advanced Filter Introduction
The value of the Items you are looking for would be *red*

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E
1
2	Red Apple	1	good	yes	2 days
3	Red dragon	3	fair	yes	1 day
4	white apple	2	average	no	3 days
5	white dragon	4	bad	no	1 week
6	yellow banana	6	good	yes	4 days
7	red mango	7	very good	no	5 days
8
9	Search	Red
10
11	Red Apple	1	good	yes	2 days
12	Red dragon	3	fair	yes	1 day
13	red mango	7	very good	no	5 days
14
MB52

Cell Formulas
Range	Formula
A11:E13	A11	=FILTER(A2:E7,ISNUMBER(SEARCH(B9,A2:A7)))
Dynamic array formulas.