Search code examples
google-sheetsgoogle-sheets-formulagoogle-query-language

Nested arrayformula

Product Price
book 10
toy 25
bag 40
Order_ID line items Total
3003 book - red,toy - red,bag - blue
3004 toy - blue
3005 bag - yellow,toy - red

I have the two tables above. The first one is a product list and the 2nd one order list. I need to calculate order total. What is the good formula for doing this?

The only way I can think of is to define column "line items" as named range line_items and then make some mid-way sheet such as

- - -
book (=ARRAYFORMULA(REGEXEXTRACT(SPLIT(index(line_items,row(),1),","),"\S+"))) toy bag
toy (=ARRAYFORMULA(REGEXEXTRACT(SPLIT(index(line_items,row(),1),","),"\S+")))
bag (=ARRAYFORMULA(REGEXEXTRACT(SPLIT(index(line_items,row(),1),","),"\S+"))) toy

and

Total - - -
=SUM(B2:2) 10(using VLOOKUP to get price) 25 40
=SUM(B3:3) 25
=SUM(B4:4) 40 25

then I can get the total from the 2nd mid-way sheet.

Is there any better way to do this using formula only? Maybe using query?

Solution

  • Here is a fully self contained formula that will do this for you:

    =ArrayFormula(MMULT(n(ARRAYFORMULA(iferror(VLOOKUP(iferror(arrayformula(left(split(B8:B10,","),  arrayformula(find(" - ",split(B8:B10,","))-1)))),$A$1:$B$4,  2,)))),(transpose(COLUMN(indirect("A1:"&ADDRESS(1,COLUMNS(iferror(arrayformula(left(split(B8:B10,","),  arrayformula(find(" - ",split(B8:B10,","))-1))))))))^0))))

    ex

    You just need to change and define the array of orders, and the array for the lookup table. In my formula, $A$1:$B$4 is the lookup table, and B8:B10 is the array of orders.

    The very first thing I did was split the items by comma:

    split(B8:B10,",")

    This split them up to be in the format of [item] - [color], each in their own cell horizontally.

    Then, I had to get the actual item from this. To do this, I used the FIND() formula.

    find(" - ",split(B8:B10,","))-1

    This gives me which character a certain string starts at. I subtracted one to get the last character of the item name.
    I then combined this with the LEFT() function.

    =iferror(arrayformula(left(split(B8:B10,","),  arrayformula(find(" - ",split(B8:B10,","))-1))))

    This takes a given number of characters from a string starting on the left hand side. By combining this with the FIND() function, I am able to extract the exact number of characters that the item name has. I also turned it into an array formula, and added IFERROR to get rid of the unnecessary errors where a blank item wasn't found.

    From here, I added it to a VLOOKUP() function.

    ARRAYFORMULA(iferror(VLOOKUP(iferror(arrayformula(left(split(B8:B10,","), arrayformula(find(" - ",split(B8:B10,","))-1)))),$A$1:$B$4, 2,)))`

    If you notice, the same equation from above is the first parameter within the VLOOKUP. The second parameter is the lookup table, and the third is the index of the lookup table that you want to return (ie 2 for the second column of the table, the prices).

    Finally, I used this formula as a template in order to calculate a sum down a row of values as an array formula:

    =ArrayFormula(MMULT(n([value]),(transpose(COLUMN([range])^0))))

    For [value] I substituted in the full VLOOKUP equation from above. This would be the array of values. Then, for [range], I had to use a dynamic equation because the number of columns cannot be strictly defined (there may be more or less items in an order). To do this, I used an INDIRECT formula:

    indirect("A1:"&ADDRESS(1,[columns])))

    I replaced [columns] with COLUMNS(iferror(arrayformula(left(split(B8:B10,","), arrayformula(find(" - ",split(B8:B10,","))-1))))), which is just counting the number of columns the item array takes up. The inner equation is the same as a previous one.

    All together, this completes the equation.