Search code examples
google-sheetsvlookuparray-formulasflattengoogle-query-language

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

Solution

  • 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