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?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,908
Messages
5,525,572
Members
409,650
Latest member
kajO

This Week's Hot Topics

Top