Assign formula array in vba
 

Hello, I loop thru my datasheet where each row's columns A,B and C have to
lookup a unique match of columns A,B and C in another workbook called "Z"
within a sheet named "Detail". For each match, I need to place the value from
cell F in the other worksheet into cell H of my datasheet. I do this by
using the following code within a loop . The error (UNABLE TO SET THE FORMULA
ARRAY PROPERTY OF THE RANGE CLASS) happens if I try to substitute a value for
the varable- valueLook(datatype Variant) such as valueLook = "AlphaBetaGamma"
instead of using cell addresses.

THIS WORKS BUT I CAN'T SUBSTITUTE A VALUE "AlphaBetaGamma" FOR VARIABLE
valueLook

valueLook = ActiveCell.Address & "&" & ActiveCell.Offset(0, 1).Address & _
"&" & ActiveCell.Offset(0, 2).Address

I USE THIS VARIABLE IN THE FOLLOWING FORMULA

Range(ActiveCell.Offset(0, 7).Address).FormulaArray =
"=INDEX('[Z.xls]Detail'!$F$4:$F$600,MATCH(" & valueLook &
",'[Z.xls]Detail'!$A$4:$A$500
&'[Z.xls]Detail'!$B$4:$B$500&'[Z.xls]Detail'!$C$4:$C$500,0))"

The basis of my question is how to assign manual values instead of cell
addresses for my lookup in FormulaArrays using the INDEX/MATCH combination
within VBA.
valueLook = "abc"
in the above code would generate the error
Thanks.