Index formula not working

[Monicasinha]

I am working in a heavy VBA and complex formula workbook. Index formula doesnt seem to be working. I am using the formula

=INDEX('Sheet1'!G417:AD516,MATCH(CONCATENATE(J5,E12,F12),'Sheet1'!A417:A516,0),MATCH(J11,'Sheet1'!G416:AD416,0)).
Result is 0.

The value however should be "JON".

On evaluating, it shows: =INDEX('Sheet1'!G417:AD516,1,1).
It is referencing to the correct cell only.
If I just put the formula =INDEX('Sheet1'!G417:AD516,1,1), it gives me the value "JON". But not through =INDEX('Sheet1'!G417:AD516,MATCH(CONCATENATE(J5,E12,F12),'Sheet1'!A417:A516,0),MATCH(J11,'Sheet1'!G416:AD416,0)).

Pls help

 

[Peter_SSs]

Hard to investigate without some sample data. With XL2BB can you make a mini sheet from the sheet that contains the formula showing E5:J12 and another mini sheet showing Sheet1 A16:H18 after hiding columns B:F?

 

[Monicasinha]

Even equal formula is not working. I mean =C3 also gives 0 if C3 has a value coming from some formula.

 

[Monicasinha]

If I change "Calculation option" to "Manual" and hit enter, then only value comes. If it is automatics, value is still 0.

 

[pjmorris]

To see what is happening in the various elements of your formula use 'Evaluate Formula' from the 'Formula Auditing' tab of the 'Formulas' Ribbon. It evaluates the formula and shows you the result of each step in your formula. It is a good way of identifying what element is causing the problem.

 

[Monicasinha]

Yes, that is what I mentioned in my question. That I did evaluate formula but still value is 0 as finally the calculation is not happening unless I select Manual and hit enter

 

[Alex Blakenburg]

Have you tried restarting your machine ?

 

[Peter_SSs]

Yes, that is what I mentioned in my question. That I did evaluate formula but still value is 0 as finally the calculation is not happening unless I select Manual and hit enter

If I change "Calculation option" to "Manual" and hit enter, then only value comes. If it is automatics, value is still 0.

Even equal formula is not working. I mean =C3 also gives 0 if C3 has a value coming from some formula.

Given all of that, what about ..

With XL2BB can you make a mini sheet from the sheet that contains the formula showing E5:J12 and another mini sheet showing Sheet1 A16:H18 after hiding columns B:F?

 

[Alex Blakenburg]

@Peter_SSs - you might want to know there is a second thread in play, which may well impact this one.

Calculation option "Automatic" not working.

In my excel sheet, calculation option "Automatic" not working. Only Manual works after I hit enter. Pls help

www.mrexcel.com

 

[Monicasinha]

I was able to solve this. Issue was that there was circular reference error but was not appearing in the error checking dropdown.