Array Formula to extract values?

[neodjandre]

Hi,

I have two columns

Col A. Col B.
"A" item1
"B" item2
"D" item3
"A" item4
"E" item5
"C" item6
"A" item7
"B" item8
... and so on..

I am looking for a formula (possibly needs to be an array formula?) to extract all items from Col B. with "A" value in col A. to another sheet.

so in this example it will be:

item1
item4
item7

any ideas please?

many thanks,
Andrew

 

[Phuoc]

Try this:

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$10="A",ROW($A$2:$A$10)),ROWS($C$1:C1))),"")

Enter with Ctrl+Shift+Enter

Or:

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$10)/($A$2:$A$10="A"),ROWS($C$1:C1))),"")

Just Enter.

 

[Fluff]

How about

+Fluff.xlsm
	A
1	B
2	Item 2
3	Item 8
4
5
6
7
8
9
Sheet2

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(INDEX(Sheet1!$B$2:$B$9,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1)/(Sheet1!$A$2:$A$9=$A$1),ROWS($A$2:$A2))),"")

 

[neodjandre]

great thanks so much !

 

[Fluff]

Glad we could help & thanks for the feedback