google-sheetsgoogle-sheets-formula

How do I do a multi-lookup and concatenate results in google sheets


I have two sheets in the same worksheet, one that has inventory with the item ID as the key, and then another that has orders. What I'm trying to figure out is how to populate a order summary column. I believe this needs a multi-lookup and then to concatenate the results.

Table 1: inventory

enter image description here

Table 2: orders

enter image description here

What I would like to do is have order items be the items in the order (order summary) ideally it would look like this:

enter image description here

I have the ability to control ID's, so I can have them be numeric or alpha (incase one makes it easier). And I have control over how the order content is saved, Im currently using CSV but if something else is easier I can use that.

Any ideas, how in google sheets, I can use a formula to complete the order items? I'm also Okay with need intermediate columns, if i need to have columns I hide that do some of the steps.

Note: I can not use Goolge Apps Script, trying to solve this via formulas.

sample sheet: https://docs.google.com/spreadsheets/d/1PoKRRgl74-devFeGIPVl6vm9KSTzfo_QlkAL3pZn490/edit#gid=1290160171


Solution

  • You may try:

    =map(A2:index(A:A,match(,0/(A:A<>""))),lambda(Σ,join(char(10)&rept("-",10)&char(10),
        map(split(Σ,","),lambda(Λ,xlookup(Λ,inventory!A:A,inventory!B:B,"🌀"))))))
    

    enter image description here