| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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???? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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???? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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???? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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???? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested IF Function | Excel Worksheet Functions | |||
| Problem with nested if function. PLS HELP | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Nested IF Function and VLookup Alternatives | Excel Worksheet Functions | |||
| Countif Function -Nested | Excel Discussion (Misc queries) | |||
Hybrid Mode
