Google Spreadsheet ArrayFormula + VLookup Transformation

user_10450471 0
Viewed 51

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

vlookup google-query-language array-formulas flatten google-sheets
1 Answers

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

user_56326290
Related