Alternatives for Nested IF(and( function. I reached the maximum 7

[newbieexceldude]

Hello there

I am very new to this forum, but already have found a lot of great help. Thanks!

However, I have a new problem. I am using the "=if(and(" function, but Excel is rejecting more than 7 functions. I will need at least 20. Is there a way around this?

The data I am working on is as follows:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Certain[/TD]
[TD]Likely[/TD]
[TD]Somewhat[/TD]
[TD]Unlikely[/TD]
[/TR]
[TR]
[TD]Reoccurring[/TD]
[TD]Green[/TD]
[TD]Green[/TD]
[TD]Blue[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Intermittent[/TD]
[TD]Red[/TD]
[TD]Purple[/TD]
[TD]Red[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Infrequent[/TD]
[TD]Yellow[/TD]
[TD]Pink[/TD]
[TD]Green[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]

The formula i've used is: =IF(AND(L4="Certain",N4="Reoccurring"),"Green (1)", IF(AND(L4="Certain",N4="Intermittent"),"Red (2)", IF(AND(L4="Certain",N4="Infrequent"),"Yellow (4)"

Do you have any suggestions?

Thank you very much.

 

[AhoyNC]

Is this what you are trying to do?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Certain	Likely	Somewhat	Unlikely
2	Reoccurring	Green	Green	Blue	Blue							Red
3	Intermittent	Red	Purple	Red	Red
4	Infrequent	Yellow	Pink	Green	Green							Certain		Intermittent
5
Sheet

 

[newbieexceldude]

Hello there:

Thank you very much for your prompt feedback!! This formula would surely work, but I think I didn't explain correctly what I am doing. The table that I showed in my question was just to show the logical arguments that need to go into the formula. My Excel sheet does not have this table.

What I have is the following:

Condition 1 Condition 2 Result
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Certain[/TD]
[TD]Reoccurring[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Likely[/TD]
[TD]Infrequent[/TD]
[TD]Pink[/TD]
[/TR]
</tbody>[/TABLE]

I have a sheet with about 2,500 rows where I could have any combination of the conditions in the first table above. What I have done is use the IF(AND function so the correct value is entered in the Result cell. However, Excel only allows a maximum of 7 functions to be nested. So I wonder if there is another function I could use. I tried CONCATENATE but it didn't work.

Any ideas?

My best regards

Is this what you are trying to do?

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

*

Certain

Likely

Somewhat

Unlikely

*

*

*

*

*

*

*

*

*

*

Reoccurring

Green

Green

Blue

Blue

*

*

*

*

*

*

*

*

*

Intermittent

Red

Purple

Red

Red

*

*

*

*

*

*

*

*

*

*

Infrequent

Yellow

Pink

Green

Green

*

*

*

*

*

*

Certain

*

Intermittent

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:97px;"><col style="width:93px;"><col style="width:64px;"><col style="width:78px;"><col style="width:86px;"><col style="width:20px;"><col style="width:21px;"><col style="width:25px;"><col style="width:20px;"><col style="width:22px;"><col style="width:18px;"><col style="width:64px;"><col style="width:34px;"><col style="width:84px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #ffff00"]Red[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula L2 =INDEX($B$2:$E$4,MATCH($N$4,$A$2:$A$4,0),MATCH($L$4,$B$1:$E$1,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

Maybe something like below.

You could put a table into the workbook, which would make it easy to make changes if needed or if you can't add a table then build the table into the formula (as in F12 below).

Excel Workbook
	A	B	C	D	E	F	G
1	TABLE			With Table
2	CertainReoccurring	Green		Certain	Reoccurring	Green
3	CertainIntermittent	Red		Likely	Infrequent	Pink
4	CertainInfrequent	Yellow
5	LikelyReoccurring	Green
6	LikelyIntermittent	Purple		Without Table
7	LikelyInfrequent	Pink		Certain	Reoccurring	Green
8	SomewhatReoccurring	Blue		Likely	Infrequent	Pink
9	SomewhatIntermittent	Red
10	SomewhatInfrequent	Green
11	UnlikelyReoccurring	Blue
12	UnlikelyIntermittent	Red
13	UnlikelyInfrequent	Green
14
Sheet

 

[newbieexceldude]

PERFECT!!! Adding the table to the formula did the trick!!

Thank you very much!!!!

Maybe something like below.

You could put a table into the workbook, which would make it easy to make changes if needed or if you can't add a table then build the table into the formula (as in F12 below).

*	A	B	C	D	E	F	G
TABLE	*	*	With Table	*	*	*
CertainReoccurring	Green	*	Certain	Reoccurring	Green	*
CertainIntermittent	Red	*	Likely	Infrequent	Pink	*
CertainInfrequent	Yellow	*	*	*	*	*
LikelyReoccurring	Green	*	*	*	*	*
LikelyIntermittent	Purple	*	Without Table	*	*	*
LikelyInfrequent	Pink	*	Certain	Reoccurring	Green	*
SomewhatReoccurring	Blue	*	Likely	Infrequent	Pink	*
SomewhatIntermittent	Red	*	*	*	*	*
SomewhatInfrequent	Green	*	*	*	*	*
UnlikelyReoccurring	Blue	*	*	*	*	*
UnlikelyIntermittent	Red	*	*	*	*	*
UnlikelyInfrequent	Green	*	*	*	*	*
*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:164px;"><col style="width:64px;"><col style="width:64px;"><col style="width:111px;"><col style="width:107px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F2 =VLOOKUP(D2&E2,A2:B13,2,0) F3 =VLOOKUP(D3&E3,A3:B14,2,0) F7 =VLOOKUP(D7&E7,{"CertainReoccurring","Green";"CertainIntermittent","Red";"CertainInfrequent","Yellow";"LikelyReoccurring","Green";"LikelyIntermittent","Purple";"LikelyInfrequent","Pink";"SomewhatReoccurring","Blue";"SomewhatIntermittent","Red";"SomewhatInfrequent","Green";"UnlikelyReoccurring","Blue";"UnlikelyIntermittent","Red";"UnlikelyInfrequent","Green"},2,0) F8 =VLOOKUP(D8&E8,{"CertainReoccurring","Green";"CertainIntermittent","Red";"CertainInfrequent","Yellow";"LikelyReoccurring","Green";"LikelyIntermittent","Purple";"LikelyInfrequent","Pink";"SomewhatReoccurring","Blue";"SomewhatIntermittent","Red";"SomewhatInfrequent","Green";"UnlikelyReoccurring","Blue";"UnlikelyIntermittent","Red";"UnlikelyInfrequent","Green"},2,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

You're welcome. Glad that worked for you. The only downside is it's a little harder to make changes if the table is hard coded into the formula.
Thanks for the feedback and welcome to the forum.