Forum Discussion
Excel replacing formula with "=#VALUE!"
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),"") ...
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