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,

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

<tbody>
</tbody>

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