3

I'm trying to use the TEXTJOIN function to create a comma separated list of the content of each row's Column D value, but only when it's Column J value reads "Regulatory" and it's Column K value reads "No". 

I can get it to do this with one condition (i.e. only dependent on Column J OR K), but not both conditions (J AND K). The formula I'm using is: 

=TEXTJOIN(",",TRUE,IF('Variable Database'!$J$3:$J$762="Regulatory"*('Variable Database'!$K$3:$K$762="No"),'Variable Database'!$D$3:$D$762,"")) 

It is a valid formula but just returns "#VALUE". Do you have any ideas about how I might achieve this?

JvdV
  • 70,606
  • 8
  • 39
  • 70
Tom Sutherland
  • 53
  • 1
  • 2
  • 6

2 Answers2

3

Unable to test this myself (I haven't got TEXTJOIN() unfortunately)

But recently I answered a fairly similar question here

So please try:

{=TEXTJOIN(",",TRUE,IF('Variable Database'!$J$3:$J$762="Regulatory", IF('Variable Database'!$K$3:$K$762="No",'Variable Database'!$D$3:$D$762,""),""))}

Note it's an array formula entered through CtrlShiftEnter

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • 1
    I have an add-on version of Textjoin with my 2010 Excel, but it doesn't work properly with arrays so I can't test it properly either! However your answer looks right. Original formula should have had additional brackets =TEXTJOIN(",",TRUE,IF(('Variable Database'!$J$3:$J$762="Regulatory")*('Variable Database'!$K$3:$K$762="No"),'Variable Database'!$D$3:$D$762,"")) – Tom Sharpe May 03 '19 at 09:47
  • @TomSharpe, I usually test `TEXTJOIN()` using Excel Online, however no array support there either :( – JvdV May 03 '19 at 09:49
  • Thanks for your help @JvdV, I tried that formula too but seems to just to write it as text into the cell. I tried removing the {} and then reapplying with ctrl shift enter but then get an error saying that the formula is incorrect? – Tom Sutherland May 03 '19 at 10:01
  • Sorry scratch that, just tried @TomSharpe 's formula which now works great. Thanks so much for your help both :-) – Tom Sutherland May 03 '19 at 10:03
  • 1
    @TomSutherland, I'll be curious to know which part of the formula wouldn't work according to Excel. It should be highlighted after you press Ok in the warning message. – JvdV May 03 '19 at 10:14
  • @JvdV it was highlighting the "No" (the value being searched for in Column K). I think the problem in my old string was incorrect bracketing according to TomSharpe. Maybe this problem also affected your formula if you'd used mine as a starting point. – Tom Sutherland May 03 '19 at 11:12
  • @TomSutherland, Ah right I see the error, it's to do with the quotes `"`. Please see edited answer :) – JvdV May 03 '19 at 11:58
  • This doesn't have to be done as an array - I used the same syntax but dropped the {} and it worked fine. – Cody Jul 02 '21 at 14:06
0

@TomSharpe 's suggestion in response to @JvdV 's comment has resolved the issue. His suggestions was:

I have an add-on version of Textjoin with my 2010 Excel, but it doesn't work properly with arrays so I can't test it properly either! However your answer looks right. Original formula should have had additional brackets =TEXTJOIN(",",TRUE,IF(('Variable Database'!$J$3:$J$762="Regulatory")*('Variable Database'!$K$3:$K$762="No"),'Variable Database'!$D$3:$D$762,""))

Tom Sutherland
  • 53
  • 1
  • 2
  • 6