Macro with working excel formula returns #Value

Hi All,

I am trying to write a macro with a formula to pull a column value from set of data without storing the formula in the cell. This formula is working totally fine with expected results when put in a cell directly. However, when I put this in a macro and run the macro, it is returning me a #value. I tried debugging line by line but I still do not understand what part exactly is causing the macro to return the error. Would anyone please help me to understand what is wrong with my macro or formula?

Formula:

Sub Macro3()

irow = 7
icol = 9
Dim j As Variant


j = Evaluate("IFERROR(IF(SUM(N(IF((Sheet3!$B:$B=$H5)*ISNUMBER(SEARCH($G5,Sheet3!$C:$C))=1," & _
"MATCH(IF((Sheet3!$B:$B=$H5)*ISNUMBER(SEARCH($G5,Sheet3!$C:$C))=1,Sheet3!$D:$D,FALSE)," & _
"IF((Sheet3!$B:$B=$H5)*ISNUMBER(SEARCH($G5,Sheet3!$C:$C))=1,Sheet3!$D:$D,FALSE),0)=" & _
"ROW($1:$6),FALSE)))>1,""Multiple"",INDEX(Sheet3!$D:$D,MATCH(1,(Sheet3!$B:$B=$H5)*" & _
"ISNUMBER(SEARCH($G5,Sheet3!$C:$C)),0))),""MISSING"")")

ThisWorkbook.Activate
Sheets("DemandForecast").Select
Cells(irow, icol).Select
Selection.Value = j
End Sub

The source data: 

Expected results: