# Indexing question

#### Jing

##### Active Member
So I have a range of cells AA7:AA58 has the following index formulas.

Code:
``````Cell - AA7: =IF(AE7=0,"",IF(\$AA\$5=1,Jan!\$C4,IF(\$AA\$5=2,Feb!\$C4,IF(\$AA\$5=3,Mar!\$C4,IF(\$AA\$5=4,Apr!\$C4,IF(\$AA\$5=5,May!\$C4,IF(\$AA\$5=6,Jun!\$C4,IF(\$AA\$5=7,Jul!\$C4,IF(\$AA\$5=8,Aug!\$C4,IF(\$AA\$5=9,Sep!\$C4,IF(\$AA\$5=10,Oct!\$C4,IF(\$AA\$5=11,Nov!\$C4,IF(\$AA\$5=12,Dec!\$C4)))))))))))))
Cell - AA8: =IF(AE8=0,"",IF(\$AA\$5=1,Jan!\$C5,IF(\$AA\$5=2,Feb!\$C5,IF(\$AA\$5=3,Mar!\$C5,IF(\$AA\$5=4,Apr!\$C5,IF(\$AA\$5=5,May!\$C5,IF(\$AA\$5=6,Jun!\$C5,IF(\$AA\$5=7,Jul!\$C5,IF(\$AA\$5=8,Aug!\$C5,IF(\$AA\$5=9,Sep!\$C5,IF(\$AA\$5=10,Oct!\$C5,IF(\$AA\$5=11,Nov!\$C5,IF(\$AA\$5=12,Dec!\$C5)))))))))))))
Cell - AA9: =IF(AE9=0,"",IF(\$AA\$5=1,Jan!\$C6,IF(\$AA\$5=2,Feb!\$C6,IF(\$AA\$5=3,Mar!\$C6,IF(\$AA\$5=4,Apr!\$C6,IF(\$AA\$5=5,May!\$C6,IF(\$AA\$5=6,Jun!\$C6,IF(\$AA\$5=7,Jul!\$C6,IF(\$AA\$5=8,Aug!\$C6,IF(\$AA\$5=9,Sep!\$C6,IF(\$AA\$5=10,Oct!\$C6,IF(\$AA\$5=11,Nov!\$C6,IF(\$AA\$5=12,Dec!\$C6)))))))))))))``````

ETC...

Is there an easier way for say indexing or some other nested formula to accomplish this without having a 5 mile long set of if statements???

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Tom Urtis

##### MrExcel MVP
Those are some long formulas to comprehend for people not familiar with your workbook. Consider explaining in words what you are working with, data in what ranges, and what your expected results are.

#### Jing

##### Active Member
Ok... i do understand that for sure.. they are quite long...

ok so i have updated things a bit since the original post...

Code:
``````Cell - AA7: =IF(AE7=0,"",IF(\$AA\$5=1,AE7,IF(\$AA\$5=2,AE7,IF(\$AA\$5=3,AE7,IF(\$AA\$5=4,AE7,IF(\$AA\$5=5,AE7,IF(\$AA\$5=6,AE7,IF(\$AA\$5=7,AE7,IF(\$AA\$5=8,AE7,IF(\$AA\$5=9,AE7,IF(\$AA\$5=10,AE7,IF(\$AA\$5=11,AE7,IF(\$AA\$5=12,AE7)))))))))))))
Cell - AA8: =IF(AE8=0,"",IF(\$AA\$5=1,AE8,IF(\$AA\$5=2,AE8,IF(\$AA\$5=3,AE8,IF(\$AA\$5=4,AE8,IF(\$AA\$5=5,AE8,IF(\$AA\$5=6,AE8,IF(\$AA\$5=7,AE8,IF(\$AA\$5=8,AE8,IF(\$AA\$5=9,AE8,IF(\$AA\$5=10,AE8,IF(\$AA\$5=11,AE8,IF(\$AA\$5=12,AE8)))))))))))))
Cell - AA9: =IF(AE9=0,"",IF(\$AA\$5=1,AE9,IF(\$AA\$5=2,AE9,IF(\$AA\$5=3,AE9,IF(\$AA\$5=4,AE9,IF(\$AA\$5=5,AE9,IF(\$AA\$5=6,AE9,IF(\$AA\$5=7,AE9,IF(\$AA\$5=8,AE9,IF(\$AA\$5=9,AE9,IF(\$AA\$5=10,AE9,IF(\$AA\$5=11,AE9,IF(\$AA\$5=12,AE9)))))))))))))``````

Here is the layout for the above formula's

Z -----------------------------AA-------- AB--------- AC
<TABLE style="WIDTH: 392pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=523><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=21 width=215>Products</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #376091; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81>Sales Made</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #376091; WIDTH: 63pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=84>Earned</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #376091; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=143>Commission Stucture</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 width=215>Product 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>\$ 1.50 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=143>\$1.50 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 width=215>Product 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>\$ 4.50 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=143>\$1.50 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 width=215>Product 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>\$ 8.00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=143>\$2.00 </TD></TR></TBODY></TABLE>

I have a combo box for the 12 months of the year. When you choose a month, say January, it will put a 1 into cell AA5, you choose February, and it will put a 2 into cell AA5, etc... depending on the month chosen it will pull the sales made from column AE... providing a view of the commission made for that month...

but having to create formulas so long it takes a long time to create these sheets, as i have to create a small if statement for every month of the year (for the specific product sold), and cram it all into one cell :P

Just looking for a way to shorten the formula's without resorting to VBA as the employee's here just want the ability to enter #'s and not have to think about other things :P

Last edited:

#### Sandeep Warrier

##### Well-known Member
Your updated formula doesn't make much sense to me.

No matter what the value of AA5 is, AE7 is always returned. So you can just have

=IF(AE7=0,"",AE7)

For your earlier formula, if you could have a table like the one in D7:D18 below, you can have

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,199
Messages
5,835,936
Members
430,396
Latest member
dzifna

### 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