Excel Lookup Formulas

GrilledCheese

Board Regular
Joined
Apr 23, 2014
Messages
75
I am using 3 syntax in one formula. They are Offset, Index, Match. I am getting a circular reference on the bottom task bar for each cell where I have the formula located. Is there a better way to use the Offset/Index/Match?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

GrilledCheese

Board Regular
Joined
Apr 23, 2014
Messages
75
=OFFSET(INDEX('[Sales.xls]Sheet1'!$A:$A,MATCH($A8,'[Sales.xls]Sheet1'!$A:$A,0)),2,0) I am getting a Circular Reference message. The Sales workbook is my source workbook. The Match lookup value is in my destination workbook.
If you post your formula, we can take a look at it.
 

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
And where is the formula located? I don't really see anything wrong with it. I copied your formula, tested on sample data, and I can't seem to get the offset to work. But removing the offset returns the expected value. So that's on my end...but it doesn't return a circular reference. Does $A8 reference the cell your are modifying?
 

GrilledCheese

Board Regular
Joined
Apr 23, 2014
Messages
75
I have two workbooks. $A8 is located in the destination folder and it is what I am referencing in the source workbook. Basically, I want to enter a lookup formula in the destination folder that looks for the value I have in $A8 from the source workbook (another workbook). Then, I want the formula to offset by 2 rows down.
And where is the formula located? I don't really see anything wrong with it. I copied your formula, tested on sample data, and I can't seem to get the offset to work. But removing the offset returns the expected value. So that's on my end...but it doesn't return a circular reference. Does $A8 reference the cell your are modifying?
 
Last edited:

GrilledCheese

Board Regular
Joined
Apr 23, 2014
Messages
75
Please disregard this post. I found the error on a different sheet in the destination folder and corrected the formula. Thanks for support.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,485
Messages
5,831,962
Members
430,098
Latest member
bemmelen

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?

Disable AdBlock

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
Top