Dynamic Absolute Cell Referencing within IF Statement

[mbeaubien]

Hey everyone,

Just had a quick question about creating a formula that would change the absolute referencing of certain cells every x amount of rows. Basically, I have a formula with 10 nested IF Statements, where one portion of the logical test needs to be dynamically absolute referenced (needs to reference 10 individual cells every 11 rows). I have tried embedding an INDIRECT, INT, ROW function to solve this problem, but it doesn't seem to be pulling correctly.

The main setup is as follows,

[TABLE="width: 1500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Column G[/TD]
[TD="align: center"]Column J[/TD]
[TD="align: center"]Column M[/TD]
[TD="align: center"]Column N[/TD]
[TD="align: center"]Column O[/TD]
[TD="align: center"]Column P[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]Column V[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Ending Period Step[/TD]
[TD]Ending Rental Period[/TD]
[TD]1st Period End Date[/TD]
[TD]2nd Period End Date[/TD]
[TD]3rd Period End Date[/TD]
[TD]4th Period End Date[/TD]
[TD][/TD]
[TD]10th Period End Date[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=IF(....View Below)[/TD]
[TD]11/30/2016[/TD]
[TD]11/30/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]New Absolute Reference[/TD]
[TD]03/31/2017[/TD]
[TD]03/31/2017[/TD]
[TD]03/31/2018[/TD]
[TD]03/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Should pull "2nd Period End Date"[/TD]
[TD]03/31/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Should Pull "3rd Period End Date"[/TD]
[TD]03/31/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Should Pull nothing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need column G to pull the "headers" (it's not a data table, nor can it be, also for reference these are underlined), based on the on the intersection of the bold rows and column J. Basically, I need the formula to find the matching date in the bolded row, the pull what step (row 2) it represents.

The only problem is that the bolded dates only appear every 11 rows (row 3, 14, 25 etc.) and that the number "steps" in column J are not static. At it's base, this is what the formula (Column G) would need to look like (there are 10 potential "Steps"):

=IF(J3=M3,$M$2,IF(J3=N3,$N$2,IF(J3=O3,$O$2,IF(J3=P3,$P$2,IF(J3=Q3,$Q$2,IF(J3=R3,$R$2,IF(J3=S3,$S$2,IF(J3=T3,$T$2,IF(J3=U3,$U$2,IF(J3=V3,$V$2,IF(J3=0,"","")))))))))))

In this, the bolded references must be absolute referenced in blocks of 11.

I've tried adding

INDIRECT("$M$"&3+INT((ROW()-3)/11))

In place of the bold references, but it doesn't seem to work properly.

Does anyone have an idea as to how to fix or change this? I would be very open to an INDEX-MATCH or HLOOKUP solution if it makes it simpler.

Thanks in advance,

Matt

 

[Chrisdontm]

Try this in cell: G3

=IFERROR(OFFSET($J$1,1,MATCH($J3,OFFSET($J$1,ROW()-1-MOD(ROW()-3,11),1,1,12),0),1,1),"")

Then copy it on down...