Welcome :)
You could try this.

Range("q2").FormulaArray = "=MATCH(1, (L1:L" & my_last_row & " =
""Sep"")" & _
" * (K1:K" & my_last_row & " = ""Cricket"")" & _
"* (D1:D" & my_last_row & " = ""Off"")" & _
" * (A1:A" & my_last_row &" < 1), 0)"

esbee wrote:
Is there a way to use a variable name, say, my_last_row in the formula where
ever the number "118" appears in the range addresses ?

Sorry for the trouble.