Hi Pete,
I'm sorry, I forgot to specify the name "values" for the column. The
formular works great! Thumb up for you, Pete. You are indeed a genius.

I'd like to know function of "*" in the formular. Thanks a million, I'm very
grateful.

Sunez


"Pete_UK" wrote:

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(IS NA(MATCH(LEFT(F1,5)*1,L_5,0))Â*,0,5),IF(ISNA(MATCH (LEFT(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