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.


[TABLE="width: 100"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Action[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]Buy[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]66
[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Sell[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Buy[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I want a table to dynamically generate the listing of the buy info
[TABLE="width: 100"]
<tbody>[TR]
[TD]Buy[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Buy[/TD]
[TD]D[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated.

GG

 

[FDibbins]

Perhaps something like this?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Action[/td][td]Value[/td][td]Buy[/td][td]Name[/td][td]Action[/td][td]Value[/td][/tr]

[tr][td]

2​

[/td][td]a[/td][td]Buy[/td][td]10[/td][td][/td][td]a[/td][td]Buy[/td][td]

10​

[/td][/tr]

[tr][td]

3​

[/td][td]B[/td][td][/td][td]66[/td][td][/td][td]D[/td][td]Buy[/td][td]

4​

[/td][/tr]

[tr][td]

4​

[/td][td]C[/td][td]Sell[/td][td]33[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]D[/td][td]Buy[/td][td]4[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
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*"