# List All Items Under Criteria

#### austinandrei

##### Board Regular
Hi,
I need help in Excel formula, maybe an Array formula but not macro.
I have 2 sheets in my Workbook.
Sheet1:
 COLUMN A COLUMN B APPLE Anna BANNANA Bella APPLE Connie BANNANA Alea BANNANA KJ BANNANA Mon BANNANA David BANNANA Louie PINEAPPLE Russel ORANGE Angelo

<tbody>
</tbody>
Then I have a dropdown list in Sheet2 C2 with values as below:
 APPLE BANNANA PINEAPPLE ORANGE

<tbody>
</tbody>
What I need is when I choose APPLE in Sheet2!C2, all the list of names meeting this criteria will be list down in Sheet 2 starting from Cell A1. So it is like Filtering Column A of Sheet1 then copying all values into Column A of Sheet2.

austin

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi,

which Excel version are you using?

Excel 2010

Hi

in C2 sheet2 = BANANA, in D2 to be copied down to get the list from B2:B1000 in sheet1

=IFERROR(INDEX(Sheet1!\$B\$2:\$B\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)-1/(Sheet1!\$A\$2:\$A\$1000=\$C\$2),ROW(A1))),"")

It's not an array formula.

Hope it helps

I Canapone, thanks for this. Which is great as it is not an array formula, easy to understand.
Just one, if the ranges changes even if I change the formula, it is no longer working.
Can you check what is wrong?
Sheet1
the previous Column A is now in Column F
the previous Column B is now in Column H
Sheet 2
instead of putting the data in Cell A1, I will put it in Cell A5
instead of C2 having the dropdown, it will be in B2.
Now my formula is below:
=IFERROR(INDEX(Sheet1!\$H\$2:\$H\$1000,AGGREGATE(15,6,ROW(\$A\$5:\$A\$1000)-1/(Sheet1!\$F\$2:\$F\$1000=\$C\$2),ROW(A1))),"")
Can you check what is wrong?
Thanks a lot!

Hi, again

this formula grabs first occurrence. You can copy the formula in A5 and drag down.

=IFERROR(INDEX(Sheet1!\$H\$2:\$H\$1000,AGGREGATE(15,6,ROW(\$A\$2:\$A\$1000)-1/(Sheet1!\$F\$2:\$F\$1000=\$C\$2),ROW(A1))),"")

Hope it helps

Ah that was great. Thanks a lot! :D

A typo: if the dropdown cell is in B2

=IFERROR(INDEX(Sheet1!\$H\$2:\$H\$1000,AGGREGATE(15,6,ROW(\$A\$
2:\$A\$1000)-1/(Sheet1!\$F\$2:\$F\$1000=\$B\$2),ROW(A1))),"")

Regards

Yes, I just change it to B2, really works. Thanks

Replies
2
Views
1K
Replies
4
Views
9K
Replies
4
Views
4K
Replies
6
Views
576
Replies
4
Views
476

1,203,324
Messages
6,054,732
Members
444,747
Latest member
Jaborsum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back