Excel VBA Match 2 Columns and Result from 3rd Column-CodePudding

Need help to compare (match) 2 columns from 2 sheets and return value from 3rd column of 2nd sheet if it matches.

    With Range("B3:B" & Range("A" & Rows.Count).End(xlUp).Row)
        .Formula = "=INDEX($D:$D,MATCH(1,(Sheet1!B$1=Sheet2!$C:$C)*(Sheet1!$A3=Sheet2!$A:$A),0))"
        .Value = .Value
    End With

Sheet 1:

Sheet 2:

This Function is taking longer than usual if I place formula in each cell (for whole month). so trying this With function but needs a better code which should run faster. Any suggestions ..

CodePudding user response：

If you have Excel 365 you can make this easily with FILTER:

My formula in cell G4 is:

=FILTER($D$2:$D$13;($A$2:$A$13=$F4)*($C$2:$C$13=G$2))

Drag to right and down

If you don't have Excel 365 you can do it with a complext formula:

=INDEX($D$1:$D$13;SUMPRODUCT(--($A$2:$A$13=$F4)*--($C$2:$C$13=G$2)*FILA($D$2:$D$13)))

Notice that the part SUMPRODUCT(--($A$2:$A$13=$F4)*--($C$2:$C$13=G$2)*FILA($D$2:$D$13)) will return the absolute row number of where the data is located so in INDEX you need to reference the whole column or substract properly (that's why I chose from row 1 including headers as first argument of INDEX).

CodePudding user response：

This uses an array formula to populate B3:E6 simultaneously

With Sheet1.Range("B3:E" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row)
    .FormulaArray = "=INDEX(Sheet2!$D$2:$D$13,MATCH(Sheet1!A3:A5&Sheet1!B1:E1,Sheet2!A2:A13&Sheet2!C2:C13,0))"
    .Value2 = .Value2
End With