Lookup Formula

[GrilledCheese]

I have a formula =OFFSET(INDEX('[Sales.xls]Sheet1'!$A:$A,MATCH($A8,'[Sales.xls]Sheet1'!$A:$A,0)),1,4). The Sales workbook is the source workbook. The $A8 in the formula is located on the destination workbook. If I insert a column in column B on the source workbook, the formula will not pull the previous lookup value. What can I do to my formula so that it will pull the same lookup value even if I insert a new column?

 

[GrilledCheese]

Also, when I close both workbooks and then open just the destination file, the cells that contain the formulas show a value of "#VALUE!". But when I open the Source workbook, the cells that contains the formulas in the destination file will now show as the lookup value. Any idea how I can show the lookup value in the destination file without opening the source workbook?

 

[Aladin Akyurek]

1) OFFSET does not work with closed books.

2) Care to word the problem you want to solve?

 

[GrilledCheese]

Basically, I want to write a formula in workbook 1 that looks up a value in workbook 2. The value is located in column A in workbook 2. But I want the formula to pull the value that is 4 rows down from the value.

1) OFFSET does not work with closed books.

2) Care to word the problem you want to solve?

 

[Aladin Akyurek]

Basically, I want to write a formula in workbook 1 that looks up a value in workbook 2. The value is located in column A in workbook 2. But I want the formula to pull the value that is 4 rows down from the value.

What are the result and match ranges of interest?

 

[GrilledCheese]

The range is in column A. I have the formula located in cell K10 in workbook 1. The formula looks for the Lookup value in cell A10 in workbook 1. the formula then looks up the lookup value in column A in workbook 2. Then the formula will pull the value that is 4 rows down from the lookup value that is located in the same column. Hope this makes sense.

What are the result and match ranges of interest?

 

[GrilledCheese]

Basically what I want is a vlookup plus 1 row down and 5 columns to the right.

 

[Aladin Akyurek]

Basically what I want is a vlookup plus 1 row down and 5 columns to the right.

Is it possible to get the range/the area in which to look up?

 

[GrilledCheese]

I found the answer online. =(Index/Match + 1) works.

Is it possible to get the range/the area in which to look up?

 

[Aladin Akyurek]

I found the answer online. =(Index/Match + 1) works.

Great. It must have been quite a task to name the ranges involved.