Macro with working excel formula returns #Value

[Chaitanya_Palley]

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:

[#2]

So that is quite a formula. I noticed that it references row 5 for a few items and row 5 is blank.
That formula also seems redundant how it keeps re-checking IF((Sheet3!$B:$B=$H5)*ISNUMBER(SEARCH($G5,Sheet3!$C:$C))=1,
after you do the 1st IF check all the sub-parts are not needed to check that anymore.
I would also not do all of this under an EVALUATE but use the VBA functionality itself instead (and it would be easier to debug then).
That aside, if you don't want a big formula inside the cell what about using a LAMBDA function instead?

[#3]

احسنت

[#4]

شكرا للافادة