# Help with offset

#### volkl77

##### Board Regular
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)))

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
At a glance, it looks like you probably want an INDEX MATCH MATCH formula, not OFFSET

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.

thanks guys, I used

Replies
7
Views
190
Replies
0
Views
236
Replies
1
Views
139
Replies
3
Views
455
Replies
12
Views
230

1,203,600
Messages
6,056,205
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back