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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]APP1[/TD]
[TD]BLAH[/TD]
[TD]SERVER1[/TD]
[TD]OS1[/TD]
[TD]PRD[/TD]
[/TR]
[TR]
[TD]APP1[/TD]
[TD]BLAH[/TD]
[TD]SERVER2[/TD]
[TD]OS2[/TD]
[TD]PRD[/TD]
[/TR]
[TR]
[TD]APP1[/TD]
[TD]BLAH[/TD]
[TD]SERVER3[/TD]
[TD]OS1[/TD]
[TD]DEV[/TD]
[/TR]
[TR]
[TD]APP1[/TD]
[TD]BLAH[/TD]
[TD]SERVER4[/TD]
[TD]OS2[/TD]
[TD]QUA[/TD]
[/TR]
[TR]
[TD]APP1[/TD]
[TD]BLAH[/TD]
[TD]SERVER5[/TD]
[TD]OS1[/TD]
[TD]DEV[/TD]
[/TR]
[TR]
[TD]APP1[/TD]
[TD]BLAH[/TD]
[TD]SERVER6[/TD]
[TD]OS2[/TD]
[TD]PRD[/TD]
[/TR]
[TR]
[TD]APP1[/TD]
[TD]BLAH[/TD]
[TD]SERVER7[/TD]
[TD]OS2[/TD]
[TD]QUA[/TD]
[/TR]
</tbody>[/TABLE]

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.