Multiple columns lookup

[Sipirili2504]

Hi. I have the below macro. I need help to amend it to return the results of column J instead of 1 or 0.
Basically I need column AD to AH in sheet "Master" to lookup column D to H in sheet "Lookup" and if any of the 2 column matches, it need to return column J from sheet "Lookup" in column AH of sheet "Master".
There are a lot of empty cells in between so the match must exclude these cells and I will then need to copy those rows with matching values to another sheet Review.
Kindly note that there are more than 10000 rows of data. Any help will be appreciated.


Dim LastRow as Integer
Lastrow = Cells(Rows.Count, 1).End(xlUp).row

With Worksheets ("Master")

.Range("AH2:AH & LastRow).Formula2R1C1= _
"IF(OR(SUMPRODUCT(--(Master!RC[07]=Lookup!C[-31]),--(Master!RC[-6]=Lookup!c[-30])--(Master!RC[-5]=Lookup!c[-29])--(Master!RC[-4]=Lookup!c[-28])--(Master!RC[-3]=Lookup!c[-27]))),(Master!RC[06]=Lookup!C[-30]),--(Master!RC[-7]=Lookup!c[-31])--(Master!RC[-5]=Lookup!c[-29])--(Master!RC[-4]=Lookup!c[-28])--(Master!RC[-3]=Lookup!c[-27])))>0,1,0)"

End with
End Sub