How to select the matching template in Excel

Question

My data is as follows:

I need to find the index of the matching template. Now I am using the complicate finction:

=IF(COUNTIF(A2,D$2)>0,1,IF(COUNTIF(A2,D$3)>0,2,IF(COUNTIF(A2,D$4)>0,3,0)))

Is there any better way?

I know that I probably could just determine UDF with VBA which would iterate through the templates range but I would avoid it.

I can find the best cell based on the template with MATCH().

But I need the opposite direction.

And I know that I could probably use Fuzzy Lookup. But I would again avoid it.

Answer 1

Since we are numbering according to the values we are looking for in the cell, I could not see a solution other than the if command. Because we are looking for more than one template and moving forward.

It's not the solution you want, but it might be a different alternative.

=IF(IFERROR(FIND($D$2,A2),0)>0,1,IF(IFERROR(FIND($D$3,A2),0)>0,2," "))