2

I am trying to nest the following two formulas, both work.

cell AO2 = Bob, cell AP2 = Judy

=TEXTJOIN(\"|\", True, AO2, AP2) = Bob|Judy

=SUBSTITUTE(ADDRESS(1,MATCH("*sumAppTags",Elements!A1:BB1,0),4),1,"") = Column Index AO

If I try:

=TEXTJOIN("|", True, SUBSTITUTE(ADDRESS(1,MATCH("*sumAppTags",Elements!A1:BB1,0),4),1,"")&2, AP2)

I get: AO2|Judy

Is there a way to get this to work?

player0
  • 124,011
  • 12
  • 67
  • 124
xyz333
  • 679
  • 6
  • 14

1 Answers1

2

try:

=TEXTJOIN("|", 1, INDIRECT(SUBSTITUTE(ADDRESS(1, 
 MATCH("*sumAppTags", Elements!A1:BB1, 0), 4), 1, )&2), AP2)
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you Player0, that did answer my question as I wrote it. but if I copy down I get an error as the `2` does not increment. Is there a way to make this dynamic so if I copy down the 2 gets incremented by 1 for each row that the formula is copied down. Thank you – xyz333 Jan 16 '20 at 21:43
  • 2
    I got it based on your solution `=TEXTJOIN("|", 1, INDIRECT(SUBSTITUTE(ADDRESS(1, MATCH("*sumAppTags", Elements!$A$1:$BB$1, 0), 4), 1, )&ROW(AO2)), AP2)` Thanks again – xyz333 Jan 16 '20 at 21:58