How can I find value follow array

In C4 Cell of Result Sheet - Array Formula - Requires CTRL+SHIFT+ENTER


=VLOOKUP(--MID(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4),"A")),255),array!$B$3:$I$32,MATCH(LEFT(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4),"A"))-1),array!$B$2:$I$2,0))

Or

=INDEX(array!$C$3:$I$32,MATCH(--MID(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4),"A")),255),array!$B$3:$B$32,0),MATCH(LEFT(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4),"A"))-1),array!$C$2:$I$2,0))



Both are array formulas.

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Try this ...

=INDEX(array!$C$3:$I$32,LOOKUP(10^10,--RIGHT(B4,ROW($1:$10))),MATCH(LEFT(B4,LEN(B4)-LEN(LOOKUP(10^10,--RIGHT(B4,ROW($1:$10))))),array!$C$2:$I$2,0))

Normal enter