I want to get ALL the data in Range1 corresponding to the keys in Range2. (shown as below)
But my formula cannot show the data properly:
=ARRAYFORMULA(TRANSPOSE(QUERY(Range1,"select Col2 where Col1 contains '"&A1:A&"'",0)))
Anyone could help to tell me the problem in my formula?
Range1
| Type | Desc |
|---|---|
| beverage | coke |
| food | cookies |
| food | bread |
| beverage | beer |
| beverage | coffee |
| food | chips |
Range2 (ideal results)
| ColA(pre-set) | ColB(arrayformula in B1) | ColC | ColD |
|---|---|---|---|
| food | cookies | bread | chips |
| beverage | coke | beer | coffee |
| food | cookies | bread | chips |
| food | cookies | bread | chips |
| beverage | coke | beer | coffee |
| beverage | coke | beer | coffee |
My exist result
| ColA(pre-set) | ColB(arrayformula in B1) | ColC | ColD |
|---|---|---|---|
| food | cookies | bread | chips |
| beverage | (cannot generate the output) | ||
| food | |||
| food | |||
| beverage | |||
| beverage |
CodePudding user response：
Assuming your source data in columns A and B, and the max column width of the expected resut is 4, try:
=arrayformula(if(len(A2:A), vlookup(A2:A, regexreplace({unique(A2:A), split(trim(transpose(query(if((transpose(unique(A2:A))=A2:A)*len(A2:A),B2:B&",",),,50000))), ",")}, ",$", ), {1, 2, 3, 4}, 0),))
and see if that helps?
CodePudding user response：
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(QUERY(IF(TRANSPOSE(A12:A17)=A2:A7,B2:B7&"