INDEX MATCH with Circular Referencing?

[MrCameronExcel]

Hi,

Is it not possible to use INDEX MATCH with circular referencing?

Problematically, I need the answer from one formula A to answer the first step of another formula B, which then allows provides the second step of formula A etc etc

Thanks

 

[pjmorris]

Try setting 'Enable Iterative Calculation' in Options. I haven't tested it with Index/Match, but I recall using it in a similar circumstance many years ago!

HTH

 

[MrCameronExcel]

Try setting 'Enable Iterative Calculation' in Options. I haven't tested it with Index/Match, but I recall using it in a similar circumstance many years ago!

HTH

View attachment 94357

I tried this but the formulas all started to equal 0 even though that wasn't the correct answer. From researching this, it appears that Excel gets confused with circular referencing even when enabled, and can start returning 0 values even though it would be incorrect.

 

[pjmorris]

I have had success with iterative calculations, but don't forget that at times more than one a solution can be valid for example sqrt(4) can be 2 or -2. I cant see your circumstances so cant comment, but I doubt that Excel is making a mistake its just that that is one of the solutions. Perhaps a way to investigate will be to use goal seek and see what that comes up with.

Sorry I cant be more help.

 

[TheRogue!]

I'm not as advanced as other users, but can Formula A be broken down into a smaller formula? For example: instead of =A+B+C (where it is relying on formula B for the value of C) could Formula A just be A+B, use that result on Formula B & then set up Formula C, using the results of Formulas A & B to get you the desired result?
Otherwise, can Formula B just be Algebraically reduced to an expression that doesn't introduce a new variable, to be used in Formula A (Say the result of Formula B is 2x+A & that equation can be substituted for C in Formula A)?