LOOKUP with TEXTJOIN

[Nanaia]

Is it possible to nest TEXTJOIN in a LOOKUP function? I want it to look up what's in B4, look for a match in an array (REF!$B$2:REF!$G$24) and TEXTJOIN what's in REF! columns F and G the same row as what it found in B4. So if the value in B4 is located at REF!B12 in the array, then TEXTJOIN REF!F12 and REF!G12. I tried nesting them but it didn't work the way I did it.

 

[Eric W]

You could do this:

=VLOOKUP(B4,Sheet10!$B$2:$G$24,5,0)&","&VLOOKUP(B4,Sheet10!$B$2:$G$24,6,0)

It works OK for 2 cells. I tried putting TEXTJOIN around a VLOOKUP with {5,6} for the column reference, but in general, lookup functions don't fare well in array formulas.