Please explain to me in steps how this formula works

[kgartin]

=LOOKUP(9.99E+307,'BALANCES - CC'!$C2:INDEX('BALANCES - CC'!2:2,MATCH(9.99E+307,'BALANCES - CC'!2:2)-Z$2))

I have no idea...

I thought Lookup wanted to find a specific SOMETHING within a specific range. But the range is what I don't get.

The portion above: INDEX('BALANCES - CC'!2:2,MATCH(9.99E+307,'BALANCES - CC'!2:2)-Z$2)
translates to 26 in my spreadsheet

But when I trade that formula for 26, the code (obviously) errors. How is this working??

 

[kgartin]

=LOOKUP(9.99E+307,'BALANCES - CC'!$C2:INDEX('BALANCES - CC'!2:2,MATCH(9.99E+307,'BALANCES - CC'!2:2)-Z$2))

I have no idea...

I thought Lookup wanted to find a specific SOMETHING within a specific range. But the range is what I don't get.

The portion above: INDEX('BALANCES - CC'!2:2,MATCH(9.99E+307,'BALANCES - CC'!2:2)-Z$2)
translates to 26 in my spreadsheet

But when I trade that formula for 26, the code (obviously) errors. How is this working??

OK...studied a second and figured out that INDEX is just an R1C1 reference so it acts as the second half of the range. Cool. Did not know you could do that in a formula.