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

  • The desired outcome is to link the different Model to the Code. in the end I would like to have a Code with what model is this Code is associated with. I have made an example on the right with 22155. Jan 17, 2019 at 3:46

1 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:

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.