INDEX, MATCH, MATCH issues with multiples

[amatsu]

I'm having some difficulty with an INDEX, MATCH, MATCH setup. I'm usually pretty good at figuring out how to make these things work after some tinkering but this one has me stumped. I have two Sheets laid out similar to the following.

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]NAME[/TD]
[TD="align: center"]Recent ON[/TD]
[TD]Recent OFF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]ON/OFF[/TD]
[TD]Item 1[/TD]
[TD]Item 2[/TD]
[TD]Item 3[/TD]
[TD]Item 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD]<--Just a label[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE1[/TD]
[TD]ON[/TD]
[TD]CODE1[/TD]
[TD]CODE2[/TD]
[TD]CODE3[/TD]
[TD]CODE4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE1[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD]CODE3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE2[/TD]
[TD]OFF[/TD]
[TD]CODE1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE2[/TD]
[TD]ON[/TD]
[TD]CODE5[/TD]
[TD][/TD]
[TD]CODE6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE3[/TD]
[TD]OFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CODE7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE3[/TD]
[TD]ON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]CODE8[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In the first sheet, A1 is the name of the second sheet. It is a drop down list that contains the names of all the rest of the sheets in the workbook. All sheets will be set up the same as the second sheet listed here. What I'm trying to do is INDEX, MATCH, MATCH the most recent ON and OFF for each Item in the columns of the second sheet to the columns in the first sheet. As you can see initially, all Items should have an "ON" date and corresponding CODE#. As time progresses, each Item will retire the code number and be assigned a new one, the date will be various for all items.


I need the formula to be as dynamic as possible. More Items may be inserted or some deleted. I have a formula that kind of works. If for example, I have the code in the cells in column B in the first sheet, it returns results of the row above my most recent "ON" in sheet 2. If there is nothing in that cell, the result is 0, of all are marked "OFF" the formula returns N/A. I know it's not a good idea to try and MATCH a cell within it's own array (I'm assuming anyways) but I cannot think of a way to do this without having a bunch of reference cells elsewhere. Which I cannot get to work anyways. I'll provide examples of that if necessary.

=INDEX(INDIRECT("'"&$A$1&"'!C3:AB34"),MATCH(MAX(IF(<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">INDIRECT("'"&$A$1&"'!$B$4:$B$<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">8")="On",INDIRECT("'"&$A$1&"'!<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">$A$4:$A$8"),0)),INDIRECT("'"&$<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">A$1&"'!$A$4:$A$8"),0),MATCH($<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">A3,INDIRECT("'"&$A$1&"'!C1:<wbr style="font-family: arial, sans-serif; font-size: 12.800000190734863px;">AB1"),0))

Thanks!