# Excel Limits Embedded "If" Statements? Workaround?

#### cameronWFA

##### New Member
Hey Everyone

I am new to the forum, as I just began a project that is going to be very long and Excel "heavy." I am sure to have plenty of questions regarding Excel, and will do my best to answer as many as I can as I go as well.

My first question for the board is:

Excel seems to only allow 7 embedded arguments for a function. Is there a way around this? Basically, I have about 15 collumns of numbers, and at the top of each collumn is a header/name. The two last collumns, however, are important. The second to last is a function that prints the largest number across the collumns per row. E.g. A2,B2,C2, <-- whichever number is highest. The last collumn function (which is the one I am having trouble with), aims to test which collumn that "Highest Number" is from, and print out the Name/Header that is at the top. However, when I go past 7 embedded "if" statements, Excel freaks out. Here is the code:

=IF(DG26=CU26, \$CU\$25, (IF(DG26=CV26, \$CV\$25, (IF(DG26=CW26, \$CW\$25, (IF(DG26=CX26, \$CX\$25, (IF(DG26=CY26, \$CY\$25, (IF(DG26=CZ26, \$CZ\$25, (IF(DG26=DA26, \$DA\$25, "NULL")))))))))))))

Is there any way around this? Thanks in advance!

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Yes, I believe 7 ifs are the limit. I work around by doing if(if(if(...))))))),0)+if(if(if(...)))))))

So if the result of one is 0, the other should be a number...

maybe you can put the results in a table and use VLookup.

Thanks for the quick response! Sorry but I am a bit confused about what you mean. Are you saying to put 0 as the "else" argument for the first 7 "ifs", and then after reaching 7 add a + amd then continue on?

Thanks!

Cam

Hey Everyone

I am new to the forum, as I just began a project that is going to be very long and Excel "heavy." I am sure to have plenty of questions regarding Excel, and will do my best to answer as many as I can as I go as well.

My first question for the board is:

Excel seems to only allow 7 embedded arguments for a function. Is there a way around this? Basically, I have about 15 collumns of numbers, and at the top of each collumn is a header/name. The two last collumns, however, are important. The second to last is a function that prints the largest number across the collumns per row. E.g. A2,B2,C2, <-- whichever number is highest. The last collumn function (which is the one I am having trouble with), aims to test which collumn that "Highest Number" is from, and print out the Name/Header that is at the top. However, when I go past 7 embedded "if" statements, Excel freaks out. Here is the code:

Is there any way around this? Thanks in advance!
Try something like this...

Book1
ABCDEF
Sheet1

This formula entered in F2:

=INDEX(A1:D1,MATCH(MAX(A2:D2),A2:D2,0))

Are you saying to put 0 as the "else" argument for the first 7 "ifs", and then after reaching 7 add a + amd then continue on?

Exactly, this will work if you're dealing with numbers only.

If you're expecting a text result, set the else/false statement to "" and then use & instead of +. So, for text it would be:
=if(if(if()))),"")&if(if(if()))))"")

Try something like this...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Header1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">15</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">89</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">20</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">75</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header2</TD></TR></TBODY></TABLE>

This formula entered in F2:

=INDEX(A1:D1,MATCH(MAX(A2:D2),A2:D2,0))

HUGE Thanks guys! I understand for the most part, conceptually, how that formula works. However, how come you must write A2:D2 again after the "MAX(A2:D2)? Also, is there a way to do this same thing for the SECOND highest value? I know that the Large function can be used for something like this but all the articles I am reading about it are just for selecting the Nth largest out of a data set in a collumn, not necessarily doing what I need it to do.

You guys are great! Thanks again!

Cam

Nevermind! Got it figured out! Here is the code in case anyone runs into this type of problem in the future.

2nd Largest:
=INDEX(\$CU\$25:\$DF\$25,MATCH(LARGE(CU26:DF26, 2),CU26:DF26,0))

Until next time!!!
Cam

This will do.
Excel Workbook
EFGHIJK
2215892075_
3
Cell Formulas
RangeFormula
F4=INDEX(F1:J1,MATCH(MAX(F2:J2),F2:J2,0))
F5=INDEX(F1:J1,MATCH(LARGE(F2:J2,2),F2:J2,0))

HUGE Thanks guys! I understand for the most part, conceptually, how that formula works. However, how come you must write A2:D2 again after the "MAX(A2:D2)? Also, is there a way to do this same thing for the SECOND highest value? I know that the Large function can be used for something like this but all the articles I am reading about it are just for selecting the Nth largest out of a data set in a collumn, not necessarily doing what I need it to do.

You guys are great! Thanks again!

Cam
You first must extract the max value. By doing that you're telling the MATCH function what to look for.

MATCH(MAX(A2:D2)

MATCH(89,

Then you have to tell it where to look for that max value:

MATCH(89,A2:D2

To return the column header that corresponds to the 2nd highest number:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">89</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">20</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">75</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header4</TD></TR></TBODY></TABLE>

This formula entered in F2:

=INDEX(A1:D1,MATCH(LARGE(A2:D2,2),A2:D2,0))

Replies
1
Views
596
Replies
3
Views
1K
Replies
1
Views
566
Replies
7
Views
573
Replies
2
Views
1K

1,206,755
Messages
6,074,756
Members
446,084
Latest member
WalmitAal

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back