-1

Demonstration of what the result should look like:

Demontration of what the result should look like

I need a formula in Google Sheets that iterates each input from input range, the look-up results for every single input will be stacked into a column per the order that the inputs appear in the range (top to down).

I tried many ways, the best I came up with is

=FLATTEN(ARRAYFORMULA(QUERY(A3:B10, "select B where A = '" & TEXTJOIN("' or A = '", TRUE, D3:D) & "'", 0)))

It cannot handle the repeated inputs and the order they appear in the input range

Improve this question

3 Answers 3

Reset to default
1

Here is a formula that I believe solves your issue:

=transpose(split(join(",",iferror(SCAN(0, D2:D10, LAMBDA(all, value, join(",",filter(B3:B10, A3:A10=value)))))),",",true))

enter image description here

I set up my sheet to match yours, so you should be able to just paste in my formula and it should work. The trick to getting it to work is the SCAN and LAMBDA expressions, used to iterate over multiple cells and compute a certain formula for each one. From there, I just had to reformat the results using 'JOIN' and 'SPLIT'.

Improve this answer
1
1

Here's one approach you may test out:

=let(Σ,tocol(,1),reduce(Σ,tocol(D2:D,1),lambda(a,c,vstack(a,ifna(filter(B:B,A:A=c),Σ)))))
Improve this answer
4
  • How does this formula work? I also found a solution, posted below, but it definitely does not use the same setup. I have never worked with vstack before, and have limited experience with tocol, lambda, and reduce, and am struggling to understand how this formula works altogether.
    – kaitlynmm569
    Commented Jun 10 at 18:17
  • In simple terms the reduce() is supplied with the input range(D2:D)→tocol() is used to disregard the blank cells in the input range→then it picks the first input now→filter() is used to extract the relevant output for the first input→this first output is stacked within vstack→then it moves onto the second input→first input steps are repeated & it keeps on stacking the extracted outputs until the last available input
    – rockinfreakshow
    Commented Jun 10 at 18:24
  • thank you! It sounds like we essentially wrote the same thing, I just took some longer steps, using JOIN and SPLIT to achieve the same thing that VSTACK allows. That's good to know, thanks again!
    – kaitlynmm569
    Commented Jun 10 at 18:30
  • 1
    another approach: =tocol(map(tocol(D2:D,1),lambda(Σ,torow(filter(B:B,A:A=Σ)))),3)
    – rockinfreakshow
    Commented Jun 10 at 18:34
0

alternative with pivot query:

=INDEX(TOCOL(TRIM(SPLIT(VLOOKUP(TOCOL(D3:D, 1), 
 TRIM(SPLIT(SUBSTITUTE(TOCOL(QUERY(QUERY(A3:B&"×", 
 "select max(Col2) group by Col2 pivot Col1"),,9^9)), 
 "×", "¤", 1), "¤")), 2, ), "×")), 3))

enter image description here

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.