A
andy62
I have a very long, array-entered formula which does a couple lookups and
concatenates the results together. I want to wrap a "RIGHT" function around
the second lookup so that I get just the last character from the text in the
retrieved cell, rather than the entire text string. But when I try it I get
an error box (and highlights the first included ROW function as the culprit.
Does anyone have a hunch why it wouldn't work?
Here is the formula now, which is entered as an array (Shift+Ctrl+Enter):
=IF(ROWS(H8:H$8)<=COUNTIF(Successors!$E$4:$L$16,$H$11),CONCATENATE(INDEX(Successors!$C$4:$C$16,SMALL(IF(Successors!$E$4:$L$16=$H$11,ROW(Successors!$E$4:$L$16)-ROW(Successors!$E$4)+1),ROWS(H8:H$8))),"
(",INDEX(Successors!$E$3:$L$3,SUMPRODUCT((INDIRECT("Successors!E"&SMALL(IF(Successors!$E$4:$L$16=$H$11,ROW(Successors!$E$4:$L$16)-ROW(Successors!$E$4)+1),ROWS(H8:H$8))+3&":L"&SMALL(IF(Successors!$E$4:$L$16=$H$11,ROW(Successors!$E$4:$L$16)-ROW(Successors!$E$4)+1),ROWS(H8:H$8))+3)=$H$11)*COLUMN(Successors!$E$4:$L$16))-4),")"),"")
The formula produces the following kind of results: President (Successor1)
I want the value inside the parentheses shortened to just the last
character, in this case "1". The second lookup, then, is the INDEX function
located inside the " (" and ")" parts of the concatenate function. I know
this is too long and complex for anyone to reproduce, so I'm grateful for any
theories/knowledge rather than a tested solution.
concatenates the results together. I want to wrap a "RIGHT" function around
the second lookup so that I get just the last character from the text in the
retrieved cell, rather than the entire text string. But when I try it I get
an error box (and highlights the first included ROW function as the culprit.
Does anyone have a hunch why it wouldn't work?
Here is the formula now, which is entered as an array (Shift+Ctrl+Enter):
=IF(ROWS(H8:H$8)<=COUNTIF(Successors!$E$4:$L$16,$H$11),CONCATENATE(INDEX(Successors!$C$4:$C$16,SMALL(IF(Successors!$E$4:$L$16=$H$11,ROW(Successors!$E$4:$L$16)-ROW(Successors!$E$4)+1),ROWS(H8:H$8))),"
(",INDEX(Successors!$E$3:$L$3,SUMPRODUCT((INDIRECT("Successors!E"&SMALL(IF(Successors!$E$4:$L$16=$H$11,ROW(Successors!$E$4:$L$16)-ROW(Successors!$E$4)+1),ROWS(H8:H$8))+3&":L"&SMALL(IF(Successors!$E$4:$L$16=$H$11,ROW(Successors!$E$4:$L$16)-ROW(Successors!$E$4)+1),ROWS(H8:H$8))+3)=$H$11)*COLUMN(Successors!$E$4:$L$16))-4),")"),"")
The formula produces the following kind of results: President (Successor1)
I want the value inside the parentheses shortened to just the last
character, in this case "1". The second lookup, then, is the INDEX function
located inside the " (" and ")" parts of the concatenate function. I know
this is too long and complex for anyone to reproduce, so I'm grateful for any
theories/knowledge rather than a tested solution.