CODEWITHSUNDEEP
google-sheetsfiltergoogle-sheets-formulatransposetextjoin
GI.JOE
GI.JOE

Reputation: 69

Multiple criteria match Google sheets

I have 2 tabs in google sheets that are

Col1  Col2
1       A
1       B
1       C
1       D
2       X
2       Y
2       Z

Col3  Col4
1      ABCD
2      XYZ

I want to fill Col4 with all matching values by joining Col3 and Col1, I have tried

=ARRAYFORMULA(Vlookup(Col3,Col1,1,0))

but getting "Result was not automatically expanded" error

Upvotes: 0

Views: 64

Answers (1)

player0
player0

Reputation: 1

try dragging:

=JOIN(, FILTER(B:B, A:A=C1))

enter image description here

or use:

=ARRAYFORMULA(SUBSTITUTE(SPLIT(TRANSPOSE(QUERY(QUERY({A:A&"♦", B:B}, 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1")
 ,,999^99)), "♦"), " ", ))

0

Upvotes: 1

Related Questions