Intersection of two lookup values

Copper Contributor

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.

7 Replies




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

best response confirmed by scheij (Copper Contributor)


I offer three solutions:



Pivot (with a helper column)


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.
In the include argument replace range="text" with ISNUMBER(SEARCH("text",range)).

@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.

Please provide a new sample file.

@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.