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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top