Excel replacing formula with "=#VALUE!"

[Wayne2016]

I'm using the following formula in a spreadsheet:

=IFERROR(INDEX('Data Q2 2020'!$A:$A,SMALL(IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000)),ROW('Data Q2 2020'!$38:$38))-2,1),"")

This formula is working very well as intended, but then Excel replaces the above formula with:

=#VALUE!

after a certain number of rows are filled with the intended formula (the first formula).  The VALUE change does not take place immediately, but after I've saved the spreadsheet.  I'm using the first formula for a large (28mg) spreadsheet to report values for over 100 clients in the past five quarters.  Again, all formulas are working well and are stable when I save the file.  It's only upon opening the file that my original formulas are changed to 

=#VALUE!

Any suggestions?

Thank you, 

Wayne

[#2]

@Wayne2016 

What do you achive with this formula?

1. This part of the formula is comparing column with a cell value which will return an array of Trues and Falses. And if it is true it will return ROW(...) but if it is false it will return FALSE which is converted to 0 for calculations. These array comparing won't work as expected in an IF formula.

=IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000))

2. This will always return 38

ROW('Data Q2 2020'!$38:$38)

Your SMALL(...)-2 will be converted in to SMALL(0,38)-2 which will then evaluated to -2.

Then INDEX(...,-2) will be evaluated as #VALUE

[#3]

@Wayne2016 

Here you can find information

How to fix the #VALUE! error

https://exceljet.net/formula/how-to-fix-the-value-error

The third-party products that this article discusses are manufactured oe Informed by companies that are independent of me. I makes no warranty, implied or otherwise, about the performance or reliability of these products.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#4]

Thank you, Nikolino, for your reply and information.

Regards,
Wayne

[#5]

You are getting #VALUE error because, the formula has to finish with Ctrl+Shift+Enter.

• Select the formula cell, press F2, and press Ctrl+Shift+Enter simultaneously.

Since you are trying to extract multiple rows, needs as array ( CSE ) formula.

It will be enclosed with curly brackets or braces { } 

{=IFERROR(INDEX('Data Q2 2020'!$A:$A,SMALL(IF('Data Q2 2020'!$H:$H=$K$30,ROW('Data Q2 2020'!$A$3:$A$3000)),ROW('Data Q2 2020'!$38:$38))-2,1),"")}