Retrieving rows from array

[mjpereira]

Hello all !

I am having difficulty with the following:
I have a sheet with lots of data; there is a specific array of data from which i need to retrieve the entire row (or selected columns), matching a simple criteria, i.e., the contents of one of the arrays´ cell.

Illustrating:

APP1	BLAH	SERVER1	OS1	PRD
APP1	BLAH	SERVER2	OS2	PRD
APP1	BLAH	SERVER3	OS1	DEV
APP1	BLAH	SERVER4	OS2	QUA
APP1	BLAH	SERVER5	OS1	DEV
APP1	BLAH	SERVER6	OS2	PRD
APP1	BLAH	SERVER7	OS2	QUA

<tbody>
</tbody>

So, i would like to extract the rows where the contents of the 5th column match "PRD" for example. That would return rows 1, 2 and 6.
I have been messing with the formula « =INDEX($M$27:$V$127;MATCH($D$4;$R$27:$R$127;0);0) » but it returns only the first column, unless i spread it over some cells, enter the formula and CTRL+SHIFT+ENTER it. Even so, i have to know beforehand the number of results.

Bottom line, is there a function that searches for a value, gathers the rows where that value is present and pastes the result (as a table or whatever) in another place ?

Thanks in advance !

 

[offthelip]

Have you tried using filters?

 

[mjpereira]

Have you tried using filters?

Hello, and thanks for your input.

I have not tried filters, i don´t believe it would give the result i want. i´d like no manual input at all.
For context, the range from which i want to extract this information comes from an external data source, updated automatically. Essentially, i want to "clip" a subset of that range, filter some information, and paste the resulting rows somewhere else.