# Convert MAX OFFSET to MAX INDEX or MAX LOOKUP

#### Ravenskeep

##### New Member
I have the following MAX OFFSET formula in one workbook that is referencing to another workbook. It returns a #VALUE! until the source workbook is open. Once opened data appears and is updated. I hoping there's a way to make it a MAX INDEX formula or some sort of MAX LOOKUP formula to resolve this issue, but I just can't seem to figure it out. Hope someone can help me.

=MAX(OFFSET('[C:\Desktop\Book1.xlsx]
1'!\$G\$34,0,0,1,\$A\$1))<o></o>
<o></o>
Formula is in cell A3 for Book1.xlsx sheet 1; in cell A4 for Book1.xlsx sheet 2 and so on.<o></o>
<o></o>
Results – If I type 4 in A1 of Book2.xlsx; then it returns the max value within Book1.xlsx G34, H34, I34, J34 (below example – Answer 199). If I type 36; then it goes through AP34; which is the last cell with data (below example – Answer 241). Row 19 in BOOK1.XLSX does have numbers 1-36.<o></o>
<o></o>
BOOK2.XLSX
 A B 1 4 2 3 =MAX(OFFSET('[C:\Desktop\Book1.xlsx]1'!\$G\$34,0,0,1,\$A\$1)) 4 =MAX(OFFSET('[C:\Desktop\Book1.xlsx]2'!\$G\$34,0,0,1,\$A\$1))

<TBODY>
</TBODY>

BOOK1.XLSX Sheet 1
 G H I J K AP 19 1 2 3 4 5 36 34 186 199 145 190 241 200

<TBODY>
</TBODY>

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Ok, thought I had it but there's still a little glitch.

Came up with:
=MAX('[C:\Desktop\Book1.xlsx]
1'!\$G\$34:INDEX('[C:\Desktop\Book1.xlsx]1'!\$G\$34:\$AP\$34,0,\$A\$1))

This works fine if both workbooks are open, but when only Book2 is open it returns the correct results when a 1 or 2 is input into \$A\$1 & the result stays without going back to #VALUE!. Great!! However; any other number (3 through 36 in this case) returns #REF!. Open Book1 a correct result appears. Close both, Open Book2 #REF! again.

Replies
0
Views
130
Replies
3
Views
195
Replies
3
Views
190
Replies
5
Views
253
Replies
8
Views
425

1,206,759
Messages
6,074,771
Members
446,086
Latest member
daywi

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

### Which adblocker are you using?

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

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