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),"") ...
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),"")}