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)*CO LUMN(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.

andy62 wrote:
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)*CO LUMN(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