Help with offset

[volkl77]

I am using the offset formula below and I keep getting a value error and I am hoping that someone can help me find the error of my ways. I keep staring at it but with no luck. Thanks!

=(OFFSET('[Trial Balances 12-13.xlsx]Summary'!$E$2:$P$687,MATCH($A26,'[Trial Balances 12-13.xlsx]Summary'!$A$4:$A$687,0),MATCH(AO$14,'[Trial Balances 12-13.xlsx]Summary'!$E$2:$P$2,0)))

 

[Scott Huish]

At a glance, it looks like you probably want an INDEX MATCH MATCH formula, not OFFSET

 

[Aladin Akyurek]

I am using the offset formula below and I keep getting a value error and I am hoping that someone can help me find the error of my ways. I keep staring at it but with no luck. Thanks!

=(OFFSET('[Trial Balances 12-13.xlsx]Summary'!$E$2:$P$687,MATCH($A26,'[Trial Balances 12-13.xlsx]Summary'!$A$4:$A$687,0),MATCH(AO$14,'[Trial Balances 12-13.xlsx]Summary'!$E$2:$P$2,0)))

Formulas do not tell well what they do, in particular when they are incomplete. It's better to describe in words what they are intended to do.

Note that OFFSET won't work with closed books. Guessing:

Either...

=VLOOKUP($A26,'[Trial Balances 12-13.xlsx]Summary'!$A$2:$P$687,MATCH(AO$14,'[Trial Balances 12-13.xlsx]Summary'!$A$2:$P$2,0),0))

Or...

=INDEX('[Trial Balances 12-13.xlsx]Summary'!$E$2:$P$687,MATCH($A26,'[Trial Balances 12-13.xlsx]Summary'!$A$2:$A$687,0),MATCH(AO$14,'[Trial Balances 12-13.xlsx]Summary'!$E$2:$P$2,0))

Adjust the ranges to suit.

 

[volkl77]

thanks guys, I used

=INDIRECT(ADDRESS(SUMPRODUCT(($A$4:$A$689=J$6)*($B$4:$B$689=$K6)*ROW($A$4:$A$689)),MATCH("Nov",$A$2:$P$2,0),1,,"Summary"))