Formula. ARRAY formula In G6 copied to full range
Formula:
=IF(SUMPRODUCT(1*(TRANSPOSE($A6:$D6)=(G$1:G$4)))>2,SUMPRODUCT(1*(TRANSPOSE($A6:$D6)=(G$1:G$4))),"")
VBA code:
Sub MatchCount()
Dim A, B, T&, Ta&, X&
A = Range("A6").CurrentRegion
B = Range("G1").CurrentRegion
ReDim C(1 To UBound(A, 1), 1 To UBound(B, 2))
For T = 1 To UBound(A, 1)
For Ta = 1 To UBound(B, 2)
X = -1 * ((A(T, 1) = B(1, Ta)) + (A(T, 2) = B(2, Ta)) + (A(T, 3) = B(3, Ta)) + (A(T, 4) = B(4, Ta)))
If X > 2 Then C(T, Ta) = X
Next Ta
Next T
Range("G6").Resize(UBound(A, 1), UBound(B, 2)) = C
End Sub
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.
Bookmarks