How to join VLOOKUP results of multiple sheets into one cell

Question

I am trying to use TEXTJOIN across multiple sheets, IF a cell is is the same I would like it to return the value from another cell in all sheets that are associated with it.

In this example, I am trying to look up the value on Sheet1 and Sheet2 for code in $B$4:$B$7 22155... and to retrieve the associated $D$4: $D$7 AAA and/or BBB and TEXTJOIN them " - ".

I have tried TEXTJOIN and combining the IF and also VLOOKUP but my brain just can't seem to work it out. This will be along a larger range of sheets and 100+ CODES in each sheet.

Any help with this would be greatly appreciated.

Answer 1

• to get all unique codes:

  =UNIQUE({QUERY(QUERY(B3:B, "select *", 0), "select Col1 where Col1 is not null", 0);
           QUERY(QUERY(F3:F, "select *", 0), "select Col1 where Col1 is not null", 0)})

• to get (joined) text per every unique code:

  =ARRAYFORMULA(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(
   "×"&IFERROR(VLOOKUP(J3:J, B3:D, 3, 0), )&" - "&
       IFERROR(VLOOKUP(J3:J, F3:H, 3, 0), )&"×", 
                "×* - *×", ""), 
                "× - "   , ""), 
                "×"      , ""))