IF Formula and more levels

[ashaari777]

Dear Gents,

I need some help please. this Formula not working ( the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format )
do you have any idea to solve this .

=IF(H2:H2="401";"A";
IF(H2:H2="403";"B";
IF(H2:H2="408";"C";
IF(H2:H2="411";"D";
IF(H2:H2="412";"E";
IF(H2:H2="413";"F";
IF(H2:H2="416";"G";
IF(H2:H2="417";"H";
IF(H2:H2="418";"I";
IF(H2:H2="419";"J";
IF(H2:H2="420";"K";
IF(H2:H2="421";"L";
IF(H2:H2="424";"M";
IF(H2:H2="425";"N";
IF(H2:H2="427";"O";
IF(H2:H2="428";"P";
IF(H2:H2="429";"Q";
IF(H2:H2="430";"R";
IF(H2:H2="431";"S";
IF(H2:H2="434";"T";
IF(H2:H2="435";"U";
IF(H2:H2="438";"V";
IF(H2:H2="439";"W";
IF(H2:H2="440";"X";
IF(H2:H2="449";"Y"))))))))))))))))))))))))

 

[James006]

Hello,

A simple solution :

1. Create a table with your 26 Letters

2. Use the vlookup() function

HTH

 

[AlanY]

deleted....

 

[mrhstn]

A table of values will certainly be better. You can hard code the table array though...

=VLOOKUP(D1, {401,"A";403,"B";408,"C";411,"D";412,"E";413,"F";416,"G";417,"H";418,"I";419,"J";420,"K";421,"L";424,"M";425,"N";427,"O";428,"P";429,"Q";430,"R";431,"S";434,"T";435,"U";438,"V";439,"W";440,"X";449,"Y"}, 2, 0)

or if you think that won't look nice you can put the table array in a defined name

MyTable - ={401,"A";403,"B";408,"C";411,"D";412,"E";413,"F";416,"G";417,"H";418,"I";419,"J";420,"K";421,"L";424,"M";425,"N";427,"O";428,"P";429,"Q";430,"R";431,"S";434,"T";435,"U";438,"V";439,"W";440,"X";449,"Y"}

=VLOOKUP(D1, MyTable, 2, 0)

 

[FormR]

A table of values will certainly be better.

I agree with this, but here is another hard coded option:

=CHAR(64+MATCH(H2,{401,403,408,411,412,413,416,417,418,419,420,421,424,425,427,428,429,430,431,434,435,438,439,440,449},0))

 

[ashaari777]

Thank you guys for your support.
I just used VLOOKUP function and it's work like what I want.

 

[James006]

You are welcome ..:wink: