Intersection of two lookup values

[scheij]

I would like to write formulas in H3:R15 to find rows in A3:E28 that match both the unique values in G3:G14 and the unique values in H2:R2.  When a match is found, the formula should return 1.  When no match is found, the result should be 0.  I believe I understand why my attempt to use a nested xlookup formula did not work in U3:AE4.  Any suggestions would be appreciated.

[#2]

@scheij 

=IF(NOT(ISNA(VLOOKUP($G3&H$2,Tag&Date1,1,FALSE))),1,0)

Maybe with this formula. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3 · Accepted solution]

@scheij 

I offer three solutions:

FILTER()

SUMIFS()

Pivot (with a helper column)

[#4]

Thank you for the solutions. Does =FILTER( ) accept wildcard characters in the criteria, like =SUMIFS( ) does?
=TRANSPOSE(UNIQUE(SORT(FILTER(Date1,(MG="=*PIT*")+(MF="PIT"))))) did not detect PITFT and OTPIT values that I added to MG.

[#5]

In the include argument replace range="text" with ISNUMBER(SEARCH("text",range)).

[#6]

@Detlef Lewin 

I recently discovered that Number1 sometimes has several values that match both criteria for Tag and for Date1.  See highlighted cells.  Adding =UNIQUE( ) to your =FILTER( ) solution restricted the results to 0 or 1.  Can you suggest how to do the same with your =SUMIFS( ) option?  I don't need to fix the Pivot Table. Vielen Dank.

[#7]

Please provide a new sample file.

[#8]

@Detlef Lewin 

I believe I found a solution by using =MAXIFS( ), rather than =SUMIFS( ).  I tried to attach the sample file to my last reply.  Here it is again.  Thanks for all your help.