CODEWITHSUNDEEP
google-sheetsvlookuparray-formulasflattengoogle-query-language
Yoann G
Yoann G

Reputation: 25

Google Spreadsheet ArrayFormula + VLookup Transformation

I have the following table on Google Spreadsheet:

enter image description here

And would need to get the following transformation result:

enter image description here

I precise that for each vegetable i can have only 1 buy price, and 1 or several sell prices

I am pretty sure this can be achieved combining ArrayFormula() + Vlookup() but have not been able to find the right formula so far. Any help would be welcomed

Thanks in advance

Cheers

Yoann

Upvotes: 1

Views: 77

Answers (1)

player0
player0

Reputation: 1

try:

=INDEX(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(
 {A2:C, IF(B2:B="buy", CHAR(13)&C2:C, C2:C)}, 
 "select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col4"), 
 "offset 1", 0)),,9^9)), " "))

enter image description here

Upvotes: 1

Related Questions