Can I use an INDEX formula to retrieve value from another INDEX Formula?

Jose Vale · 06/19/20

Hello,

I have two similar INDEX formulas, where one works and the other one retrieves #INVALID VALUE error.

They are as follows:

"IF([Nov-19]188 = 0, 0, INDEX($[Jan-19]$119:$[FY50]$119, 1, [Nov-19]188))" - this one works and retrieves the value I want.

"=IF([Nov-19]110 = 0, 0, INDEX($[Jan-19]$189:$[FY50]$189, 1, [Nov-19]110))" - retrieves #INVALID VALUE and line 189 is where the above formula is.

Is it possible to use an INDEX formula to retrieve value from another INDEX Formula?

Paul Newcome · 06/19/20

Yes. That was going to be my next question. Glad you got it figured out! 👍️

Paul Newcome · 06/19/20

What exactly is in [Nov-19]110?

Jose Vale · 06/19/20

I have figured out the problem since, the range $[Jan-19]$189:$[FY50]$18 included #INVALID VALUEs which led the formula into error.

Paul Newcome · 06/19/20

Yes. That was going to be my next question. Glad you got it figured out! 👍️

Best Answer