Google-sheets – How to join VLOOKUP results of multiple sheets into one cell

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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.

enter image description here

Best Answer

  • 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), )&"×", 
              "×* - *×", ""), 
              "× - "   , ""), 
              "×"      , ""))

5

Related Topic