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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
=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.
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
Please disregard this post. I found the error on a different sheet in the destination folder and corrected the formula. Thanks for support.
 
Upvote 0

Forum statistics

Threads
1,218,898
Messages
6,145,075
Members
450,590
Latest member
Naneng

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
Back
Top