Creating a list from a named range

[GuyGadois]

I have a spreadsheet that lists a number of items, the second columns says if it will be Sold or Purchased and then the third column has a value. I would like to create a list that takes all the "Sell" from the list above and lists them with the value in the column next to the name. I am fairly sure I should use an Array and Match but unsure how to do it.

Name	Action	Value
a	Buy	10
B		66
C	Sell	33
D	Buy	4

<tbody>
</tbody>

I want a table to dynamically generate the listing of the buy info

Buy	A	10
Buy	D	4

<tbody>
</tbody>

Any help is greatly appreciated.

GG

 

[FDibbins]

Perhaps something like this?

	A​	B​	C​	D​	E​	F​	G​
1​	Name	Action	Value	Buy	Name	Action	Value
2​	a	Buy	10		a	Buy	10​
3​	B		66		D	Buy	4​
4​	C	Sell	33
5​	D	Buy	4

D1 contains your criteria
E2=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$5=$D$1,ROW($A$2:$A$5)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.

Copy down, then copy all across as needed

 

[GuyGadois]

That works well. Thank you for helping out. One additional question. Is it possible to make the generated list list "Buy" and "Sell" data (multiple Actions not just one)?

Guy?

 

[FDibbins]

If you will only have Buy, Sell or nothing, try this toi get both Buy and Sell
=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$5<>"",ROW($A$2:$A$5)),ROWS($A$1:A1))),"")
ARRAY entered

 

[GuyGadois]

Actually, I have 4 variables. I would like to make a list that contains "Sell" and "Sell All". I think it is best to use If(or()) but I can't seem to get it to work. Any help is very appreciated.

This is what I am using which doesn't work:

=IFERROR(INDEX(C:C,SMALL(IF(OR($T$4:$T$200="Sell",$T$4:$T$200="Sell All")),ROW($C$4:$C$200)),ROWS($C$1:C1))),"")

C:C - name
T = action

Guy

If you will only have Buy, Sell or nothing, try this toi get both Buy and Sell
=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$5<>"",ROW($A$2:$A$5)),ROWS($A$1:A1))),"")
ARRAY entered

 

[GuyGadois]

Can anyone help with using the "OR" function within an index function?

 

[FDibbins]

Do you have any other "sell" option? If not, ou could use "sell*"