Nested And function
 

I i have more than 7 nested level like the following what can be done?
=IF(AND(F11="Ma",G11="R"),"M",
(IF(AND(F11="Ma",G11="O"),"H",
IF(AND(F11="Ma",G11="F"),"H",
IF(AND(F11="Mo",G11="R"),"L",
IF(AND(F11="Mo",G11="O"),"M",
IF(AND(F11="Mo",G11="F"),"H",
IF(AND(F11="Mi",G11="R"),"L",.....))))))))

Actually i only have 9 level and so i am shortfall of 2 to complete my
formulas.... andyone can help????

Nested And function
 

This seemed to work okay for me.

=INDEX({"M","H","H","L","M","H","H","L"},MATCH(F11 &G11,{"MaR","MaO","MaF","MoR","MoO","MoF","MiR"},0 ))

Alternatively, create a table like this

A B
1 MaR M
2 MaO H
3 MaF H

and use VLOOKUP(F11&G11, A1:B3, 2, FALSE)

Change the reference A1:B3 to wherever you put your table.



"Kelvin Pek" wrote:

I i have more than 7 nested level like the following what can be done?
=IF(AND(F11="Ma",G11="R"),"M",
(IF(AND(F11="Ma",G11="O"),"H",
IF(AND(F11="Ma",G11="F"),"H",
IF(AND(F11="Mo",G11="R"),"L",
IF(AND(F11="Mo",G11="O"),"M",
IF(AND(F11="Mo",G11="F"),"H",
IF(AND(F11="Mi",G11="R"),"L",.....))))))))

Actually i only have 9 level and so i am shortfall of 2 to complete my
formulas.... andyone can help????

Nested And function
 

Thanks Pal

Regards
Kelvin Pek

"JMB" wrote:

This seemed to work okay for me.

=INDEX({"M","H","H","L","M","H","H","L"},MATCH(F11 &G11,{"MaR","MaO","MaF","MoR","MoO","MoF","MiR"},0 ))

Alternatively, create a table like this

A B
1 MaR M
2 MaO H
3 MaF H

and use VLOOKUP(F11&G11, A1:B3, 2, FALSE)

Change the reference A1:B3 to wherever you put your table.



"Kelvin Pek" wrote:

I i have more than 7 nested level like the following what can be done?
=IF(AND(F11="Ma",G11="R"),"M",
(IF(AND(F11="Ma",G11="O"),"H",
IF(AND(F11="Ma",G11="F"),"H",
IF(AND(F11="Mo",G11="R"),"L",
IF(AND(F11="Mo",G11="O"),"M",
IF(AND(F11="Mo",G11="F"),"H",
IF(AND(F11="Mi",G11="R"),"L",.....))))))))

Actually i only have 9 level and so i am shortfall of 2 to complete my
formulas.... andyone can help????

Nested And function
 

You're welcome.

"Kelvin Pek" wrote:

Thanks Pal

Regards
Kelvin Pek

"JMB" wrote:

This seemed to work okay for me.

=INDEX({"M","H","H","L","M","H","H","L"},MATCH(F11 &G11,{"MaR","MaO","MaF","MoR","MoO","MoF","MiR"},0 ))

Alternatively, create a table like this

A B
1 MaR M
2 MaO H
3 MaF H

and use VLOOKUP(F11&G11, A1:B3, 2, FALSE)

Change the reference A1:B3 to wherever you put your table.



"Kelvin Pek" wrote:

I i have more than 7 nested level like the following what can be done?
=IF(AND(F11="Ma",G11="R"),"M",
(IF(AND(F11="Ma",G11="O"),"H",
IF(AND(F11="Ma",G11="F"),"H",
IF(AND(F11="Mo",G11="R"),"L",
IF(AND(F11="Mo",G11="O"),"M",
IF(AND(F11="Mo",G11="F"),"H",
IF(AND(F11="Mi",G11="R"),"L",.....))))))))

Actually i only have 9 level and so i am shortfall of 2 to complete my
formulas.... andyone can help????