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). Hope you could help. Thank you.
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
user23576324 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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))
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 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’.