Return a selection of data based on a filter in another cell

Question

I tried to look but I was unable to find this information anywhere on the site.

I am trying to return a selection of my data that fits a criteria in another cell.

I have a sheet (Sheet1) that has ALL my data in it (around 38000 rows) and I need to filter this on another sheet(Sheet2) for one of the columns in this data. For example, if I wanted to return all members that had a 7 in column P on Sheet1? and return all the columns that appeared in Sheet1 in Sheet2

So far I am trying the INDEX(array,MATCH()) way of working but this is only returning the first person that has a 7 in column P and not each individual member. And if I do it on a line by line basis, there will be gaps in the data where it doesn't match and want them all bunched to the top. (I hope that makes sense)

Sheet1 Sheet2

The results show in Sheet2 is using the following formula:

=INDEX(Sheet1!A:A,MATCH($B$1,Sheet1!I:I,0))

Is this something that can actually be achieved?

Please let me know if I am not clear in anything I have asked.

@GMalc - Due to the size of the data, and the use of the data I'm trying to stop any human interaction needed for the movement of that data. Thank you, that could be a work around. — Hatton90
@OverflowStacker I don't know how to provide sample data in the question, sorry. In this example Column P in Sheet1 would have a value between 1 and 12. I want to have a field in Sheet2 that I input a value and it returns all the rows where Column P is that value. I need all the rows to copy over from the original data (for now, I will bring this down at a later date) Sorry not sure how else to show. — Hatton90
I don't know how to provide sample data, I'm sorry. As stated in my comment to @OverflowStacker I have ~40,000 rows of data on Sheet1, each month I need to make updates to this data and provide an output. Which rows need to be updated is based on their value in Column P which is a value between 1 and 12. I want to have a separate sheet, where I can input a value between 1 and 12, and then it searches through the data on Sheet1 for those members which that value in Column P. It will then return all the members that match this criteria on this second sheet. — Hatton90
@GMalc - I have tried =INDEX('Sheet1'!A:A,MATCH($B$1,'Sheet1'!P:P,0)) and copied this down for 40,000 rows but it only returns the first member that satisfies the criteria. I then tried =INDEX('Sheet1'!A2:A2,MATCH($B$1,'Sheet1'!P2:P2,0)) and then copied down for each row, but this will come back with errors for those that don't satisfy the criteria. As I said, I want this to be automatic rather than manual and currently I am doing a similar thing to AutoFilter so not sure this will make it any more efficient than I currently have. — Hatton90

jblood94 jblood94 · Accepted Answer · 2020-07-08T20:15:33

You're on the right track with INDEX, but you're also right that MATCH will only get you the first result. You need an array formula that will carry through the rows you want to retrieve. Here's a simple example:

To get this, select H2:J21 and enter this as an array formula (commit with Ctrl + Shift + Enter):

=IFERROR(INDEX(A2:C21,SMALL(IF(C2:C21=E2,ROW(A2:A21)-ROW(A1),FALSE),ROW(A2:A21)-ROW(A1)),COLUMN(A1:C1)),"")

UPDATE: As @OverflowStacker points out, for your example problem, since your table headers are on row 1 and it doesn't look like you would ever have your column header in your lookup column's data, you could use a simpler formula:

=IFERROR(INDEX(A1:C21,SMALL(IF(C1:C21=E2,ROW(A1:A21),FALSE),ROW(A1:A21)),COLUMN(A1:C1)),"")

Return a selection of data based on a filter in another cell

1 Answers