I have an IFERROR+INDEX+MATCH formula to return a child action number based on the parent action number. The parent can have multiple child actions, but I just need one. They're written as a string in the report as 123451234512345 for a parent with three child actions. We now have actions with 6 digits. Here is the original formula:

=IFERROR(VALUE(RIGHT(INDEX('Source Data'!G:G,(MATCH(B15,'Source Data'!D:D,0))),5)),"")

If the parent is abcde and the child is 12345, the code returns 12345. If the parent has a child as 123456, the code returns 56 (the actual action number for example is 100025, so the zeros get dropped). If I change the 5 to a 6, then it returns the blank, since 512345 doesn't exist. I tried to include OR in the formula, but it only returned TRUE rather than the action number. Not sure what I should do. I need a code to return either a 5-digit or a 6-digit action number that's stored as a string.

解决方案

=IFERROR(VALUE(RIGHT(INDEX(TEXTSPLIT(INDEX('Source Data'!G:G, MATCH(B16, 'Source Data'!D:D, 0)), CHAR(10)), COUNTA(TEXTSPLIT(INDEX('Source Data'!G:G, MATCH(B16, 'Source Data'!D:D, 0)), CHAR(10)))), MIN(LEN(INDEX(TEXTSPLIT(INDEX('Source Data'!G:G, MATCH(B16, 'Source Data'!D:D, 0)), CHAR(10)), COUNTA(TEXTSPLIT(INDEX('Source Data'!G:G, MATCH(B16, 'Source Data'!D:D, 0)), CHAR(10))))), 6))), "")

CAPRA gave me this (after lots of trial and error) and it works. It returns the last child action in the string.