List All Items Under Criteria

austinandrei

Board Regular
Joined
Jun 7, 2014
Messages
117
Hi,
I need help in Excel formula, maybe an Array formula but not macro.
I have 2 sheets in my Workbook.
Sheet1:
COLUMN ACOLUMN B
APPLEAnna
BANNANABella
APPLEConnie
BANNANAAlea
BANNANAKJ
BANNANAMon
BANNANADavid
BANNANALouie
PINEAPPLERussel
ORANGEAngelo

<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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Canapone

Active Member
Joined
May 10, 2007
Messages
463
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
 

austinandrei

Board Regular
Joined
Jun 7, 2014
Messages
117

ADVERTISEMENT

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!
 

Canapone

Active Member
Joined
May 10, 2007
Messages
463
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
 

austinandrei

Board Regular
Joined
Jun 7, 2014
Messages
117

ADVERTISEMENT

Ah that was great. Thanks a lot! :D
 

Canapone

Active Member
Joined
May 10, 2007
Messages
463
Thanks for your kind feedback.


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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top