Returning only one column of names from a range where an "x" designation exists on a different sheet

I have a range that has a lot of details for a list of names. I have a column called "include" in that range. I'm trying to create a series of summaries that would be on different sheets and would pull from the master sheet. Each sheet would populate all the names that are marked with an "x" in the include column.

This will change a lot, so I was thinking some sort of array function. I'm also tried to play around with the advanced filter but I only really want to include the name, not all the filtered data as the new sheet might only be a list of the names that are marked include and just the location (as an example)

Solution 1:

FILTER

Since 2018 the FILTER function was introduced to Excel 365 and will appear in Excel 2021. From the docs it's pretty straight-forward on what the application would look like.

=FILTER(A2:E5,F2:F5="x")

A2:E5 is the cell range you want to have in your results, F2:F5 the rows from which you want to filter.

MATCH

My best pre-filter-function approach is kind of a mess, but I'll give it a shot with MATCH. Assume I have the following setup

In column H I want to get the rows at which the x appear. Getting the first x is pretty simple with a single MATCH call. In H2 I have the following.

=MATCH("x",$F:$F,0)

For the next rows I want to use MATCH again, the column range however must start one row after the last x found. I'll do that using INDIRECT which gives me the following for H3.

=H2+MATCH("x",INDIRECT("F"&(H2+1)&":F"&ROWS(F:F)),0)

From H3 I can drag everything to the last row of the table. Note that the #NV errors don't bug us just yet.

What should appear in the H column then is this.

<empty>
3
5
#NV
#NV

Or, written as cell formulas:

<empty>
=MATCH("x",$F:$F,0)
=H2+MATCH("x",INDIRECT("F"&(H2+1)&":F"&ROWS(F:F)),0)
=H3+MATCH("x",INDIRECT("F"&(H3+1)&":F"&ROWS(F:F)),0)
=H4+MATCH("x",INDIRECT("F"&(H4+1)&":F"&ROWS(F:F)),0)

From there we could use a matrix formula in I2 to copy the data from the original table.

=IFERROR(INDIRECT("A"&H2&":E"&H2),"")

You can use ctrl+shift+enter to enter the matrix formula or, better yet, see if just hitting enter gives you an array formula. After that you should be able to drag that cell down the table to get a result that looks something like this. Feel free to hide the H column after that so those #NV become invisible.

EDIT: I manually had to translate the formulas into English, I hope no errors slipped in accidentally