CODEWITHSUNDEEP
excelvbafilterexcel-formula
Hatton90
Hatton90

Reputation: 3

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

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.

Upvotes: 0

Views: 734

Answers (1)

jblood94
jblood94

Reputation: 17011

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:

enter image description here

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)),"")

Upvotes: 1

Related Questions