Did you remember to set up named ranges for L_7, L_8 and L_9 in your
actual data table? Obviously, these relate to 7-, 8- and 9-digit
numbers. #NAME? means that Excel does not recognise the name of a
function or named range.

Your amendments to the formula seem to be okay.

Pete

On Aug 28, 5:22*pm, Sunez wrote:
Thanks Pete. Your formular works very well on the example I gave but was
giving #NAME? error thoughout when I tried it on actual data. Just trying to
figure out what could be the cause. Take a look at the formula in case I made
a mistake somewhere, I modified it to accomodate other number of digits.

=INDEX(Table,MATCH(LEFT(F1,MAX(IF(ISNA(MATCH(LEFT( F1,1)*1,L_1,0)),0,1),IF(I*SNA(MATCH(LEFT(F1,2)*1,L _2,0)),0,2),IF(ISNA(MATCH(LEFT(F1,3)*1,L_3,0)),0,3 )*,IF(ISNA(MATCH(LEFT(F1,4)*1,L_4,0)),0,4),IF(ISNA (MATCH(LEFT(F1,5)*1,L_5,0))*,0,5),IF(ISNA(MATCH(LE FT(F1,6)*1,L_6,0)),0,6),IF(ISNA(MATCH(LEFT(F1,7)*1 ,L_*7,0)),0,7),IF(ISNA(MATCH(LEFT(F1,8)*1,L_8,0)), 0,8),IF(ISNA(MATCH(LEFT(F1,9)**1,L_9,0)),0,9)))*1, values,0),2)

Pete, I really appreciate your efforts.

Sunez