Google Sheets arrayformula vlookup transpose matching values and return all unique values in another sheet

1
votes

I need an arrayformula to lookup all matching cells in another sheet and transpose and return all corresponding unique values.

Here is my sheet:

https://docs.google.com/spreadsheets/d/1uqeM6M9MAPehgyqyRLmH9mgg3Jh_RUSxADMisarht5Y/edit?usp=sharing

I've tried this but it doesn't work:

=ARRAYFORMULA(IFERROR(VLOOKUP(TRANSPOSE(A2:A), Sheet2!A2:B, 2, 0)))
1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
could you please add in your sheet an example of the expected outcome? – Benoît Wéry
Ok no problem. Thats added now. – chappers

1 Answers

0
votes
=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, 
 {SORT(UNIQUE(INDIRECT("Sheet2!A2:A"&COUNTA(Sheet2!A2:A)+1))), 
 SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(ISNUMBER(QUERY(QUERY(UNIQUE(Sheet2!A2:B), 
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2", 0), "offset 1", 0)), 
 "♦"&QUERY(UNIQUE(Sheet2!A2:B), 
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2 limit 0", 0), ))
 ,,999^99))), "♦")}, {1,2,3,4,5,6}, 0))

0