LOOKUP a key range in another key range to get ALL corresponding values, instead of just the first

user_8572382 0
Viewed 113

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
array-formulas google-sheets-formula google-sheets one-to-many lookup
2 Answers

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?

enter image description here

user_38894230 
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(QUERY(IF(TRANSPOSE(A12:A17)=A2:A7,B2:B7&"",),,2^9)),"")))

Size: 95 chars

Explanation:

  • Create a 2D array comparing Range 1(A2:A7) vs transposed Range2(A2:A17)

    =ARRAYFORMULA(TRANSPOSE(A12:A17)=A2:A7)
Range 1 v Range2 -> food beverage food food beverage beverage
beverage FALSE TRUE FALSE FALSE TRUE TRUE
food TRUE FALSE TRUE TRUE FALSE FALSE
food TRUE FALSE TRUE TRUE FALSE FALSE
beverage FALSE TRUE FALSE FALSE TRUE TRUE
beverage FALSE TRUE FALSE FALSE TRUE TRUE
food TRUE FALSE TRUE TRUE FALSE FALSE

  • Change TRUEs to Range1 Description(B2:B7) and a delimiter :

    =ARRAYFORMULA(IF(TRANSPOSE(A12:A17)=A2:A7,B2:B7&"",))
Range 1 v Range2 -> food beverage food food beverage beverage
beverage coke coke coke
food cookies cookies cookies
food bread bread bread
beverage beer beer beer
beverage coffee coffee coffee
food chips chips chips

  • If you look vertically in each column, you have all the desired values for each required header. At this point, we use a classical query to do a vertical join:

    =ARRAYFORMULA(QUERY(IF(TRANSPOSE(A12:A17)=A2:A7,B2:B7&"",),,2^9))
Range 1 v Range2 -> food beverage food food beverage beverage
beverage cookies bread chips coke beer coffee cookies bread chips cookies bread chips coke beer coffee coke beer coffee
food
food
beverage
beverage
food

  • Now, we just TRANSPOSE and SPLIT by the delimiter:

    =ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(QUERY(IF(TRANSPOSE(A12:A17)=A2:A7,B2:B7&"",),,2^9)),"")))
Range 2 v - - - - - -
food cookies bread chips
beverage coke beer coffee
food cookies bread chips
food cookies bread chips
beverage coke beer coffee
beverage coke beer coffee
user_84044530
Related