Error when insering a "Right" function

[Guest]

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.

 

[Franz Verga]

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.

Hi Andy,

maybe you can upload a small example file to www.savefile.com...

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy