I have a dataset consisting of medical diagnosis (ICD codes) that need to be compared against a list of specific ICD codes to see if a patient met specific criteria.

I've tried using

 Range("Table1[Column1]") =  "=if((Sumproduct(Countifs([@diagnosis],Criteria_Table[Lookup Values],0))
But given I have over 1000 individual patient records, each with multiple diagnosis codes and about 3000 specific diagnosis codes to search for, this takes a very long time (30+ minutes)

I tried setting both ranges to an array and looping through, which is a bit faster, but still way too slow:

Dim criteria as variant: criteria = Range("Criteria_Table[Lookup Values]").value
Dim diag as variant: diag = Range("Table1[Diagnosis]").value
For i = 1 to UBound(diag)
If Not Iserror(Application.Match(diag(I,1), criteria, 0)) Then
Range("Table1[Column1]") = 1
Else
Range("Table1[Column1]") = 0
End If
Next i
Is there any faster way to do this?