0

I am trying to do a TEXTJOIN based on multiple columns, in this case Column B and D within a selected range (If column A = G9 and column C = H9) while ignoring items in Column D that are not in range G2:G5 (if Column D = an item in range G2:G5). Everything I try seems to result in NA#

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70
Chris Bak
  • 21
  • 7

1 Answers1

0

Would this work:

=TEXTJOIN(" | ",TRUE,IF(A2:A17=G9,IF(C2:C17=H9,IF(D2:D17=TRANSPOSE(G2:G5),E2:E17,""))))

Confirmed through CtrlShiftEnter

Unfortunately I don't have textjoin and am unable to test myself.

To elaborate, I beleive you are needing the TRANSPOSE() function. Right now you are comparing a vertical range against a vertical range. This can cause problems, the N/A# error, due to the different size of the arrays you are comparing. I myself was confused by this behaviour and asked a question on this matter on here. The answer given was very usefull to elaborate on this Excel behaviour.

Hopefully this solves your issue :)

EDIT

I wish I could play around with TEXTJOIN() to help you out, but to have a temporary patch until someone actually can help you further, you could try implement some SUBSTITUTE() functions to catch the FALSE values like so:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(" | ",TRUE,IF(A2:A17=G9,IF(C2:C17=H9,IF(D2:D17=TRANSPOSE(G2:G5),E2:E17,""))))," | FALSE ",""),"| FALSE",""),"FALSE | ","")

Entered as array

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thanks, it's getting much closer than the NA#s I was getting before. It's capturing the right comments, but adding a bunch of FALSES in between: comment 1 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | comment 4 | FALSE | FALSE | FALSE | FALSE | comment 6 | FALSE | FALSE | FALSE | FALSE | comment 9 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE – Chris Bak May 22 '19 at 12:35
  • @ChrisBak, I wish I had an Excel 2019 license to play around with TEXTJOIN() myself to get rid of those FALSE values for you. Maybe try edited answer for a temporary 'patch' – JvdV May 22 '19 at 13:09
  • Thanks JvdV, that works pretty great: comment 1 | comment 4 | comment 6 | comment 9 – Chris Bak May 23 '19 at 00:42
  • I am having another strange problem when I try to use dynamic named ranges, it seems to change the range. For example, =OFFSET('ABAK data'!F$2,0,0,COUNTA('ABAK data'!$F:$F),1) will change the range formula to: =OFFSET('ABAK data'!XEV$2,0,0,COUNTA('ABAK data'!$F:$F),1) as soon as I hit CTR-CHIFT-ENTER and I wind up with a Value# – Chris Bak May 23 '19 at 08:48