Filter and Sort the data from another sheet

Question

I have a stock xls with two sheets:

TRN HISTORY sheet

with DATE, SYMBOL, UNITS, PRICE, TYPE, OWNED columns

DATE	SYMBOL	UNITS	PRICE	TYPE	OWNED
1.1.2022	O	10	49.6	Dividend	Yes
2.2.2022	IRM	20	31.9	Dividend	No
3.3.2022	AAL	50	14.0	Growth	Yes

STOCKS sheet

with SYMBOL, NAME, STOCK TYPE, UNITS, PRICE columns

SYMBOL	NAME	STOCK TYPE	UNITS	PRICE
O	Realty Income Corp	Dividend	10	49.6

I want to filter out only stocks that are marked as Yes in the column OWNED (TRN HISTORY sheet) and show them on the main STOCK sheet with the corresponding price, number of units, and stock type.

I was able to do that via

={"SYMBOL";SORT(FILTER('TRN HISTORY'!B:B;'TRN HISTORY'!F:F="Yes"))}

But I do not know how to get the corresponding values from other columns like the number of units purchased and the price once it's sorted alphabetically.

Here is the demo xls sheet:

https://docs.google.com/spreadsheets/d/1NpP9IXtQ-Da8t_5G0vfnMXb1o_62tQbR_GuWZTB2GWc/edit?usp=sharing

Thank you in advance!

Answer 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A1:A, 
 FILTER('TRN HISTORY'!B:F, 'TRN HISTORY'!F:F<>"no"), {4, 2, 3}, 0)))

---

UPDATE:

=QUERY(FILTER({'TRN HISTORY'!B:E, {"NAME"; 
 BYROW('TRN HISTORY'!B2:B, LAMBDA(x, GOOGLEFINANCE(x, "name")))}}, 
 REGEXMATCH('TRN HISTORY'!F:F, "(?i)yes|owned")), 
 "select Col1,Col5,Col4,Col2,Col3 order by Col1", 1)