IF Formula and more levels

ashaari777

New Member
Joined
Oct 17, 2016
Messages
10
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

A simple solution :

1. Create a table with your 26 Letters

2. Use the vlookup() function

HTH
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,979
Office Version
365, 2019, 2016
Platform
Windows
deleted....
 
Last edited:

mrhstn

Active Member
Joined
Jul 25, 2017
Messages
316
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,275
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 17, 2016
Messages
10
Thank you guys for your support.
I just used VLOOKUP function and it's work like what I want.
 

Forum statistics

Threads
1,085,522
Messages
5,384,187
Members
401,885
Latest member
nirmalpatel85

Some videos you may like

This Week's Hot Topics

Top