Error when insering a "Right" function
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. |
Error when insering a "Right" function
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 |
All times are GMT +1. The time now is 08:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com