SOLVED

Intersection of two lookup values

%3CLINGO-SUB%20id%3D%22lingo-sub-3066082%22%20slang%3D%22en-US%22%3EIntersection%20of%20two%20lookup%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3066082%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20write%20formulas%20in%20H3%3AR15%20to%20find%20rows%20in%20A3%3AE28%20that%20match%20%3CU%3Eboth%3C%2FU%3E%20the%20unique%20values%20in%20G3%3AG14%20and%20the%20unique%20values%20in%20H2%3AR2.%26nbsp%3B%20When%20a%20match%20is%20found%2C%20the%20formula%20should%20return%201.%26nbsp%3B%20When%20no%20match%20is%20found%2C%20the%20result%20should%20be%200.%26nbsp%3B%20I%20believe%20I%20understand%20why%20my%20attempt%20to%20use%20a%20nested%20xlookup%20formula%20did%20not%20work%20in%20U3%3AAE4.%26nbsp%3B%20Any%20suggestions%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3066082%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3066092%22%20slang%3D%22en-US%22%3ERe%3A%20Intersection%20of%20two%20lookup%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3066092%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F705036%22%20target%3D%22_blank%22%3E%40scheij%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(NOT(ISNA(VLOOKUP(%24G3%26amp%3BH%242%2CTag%26amp%3BDate1%2C1%2CFALSE)))%2C1%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20this%20formula.%20Enter%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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

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

best response confirmed by scheij (Occasional Contributor)
Solution

@scheij 

I offer three solutions:

FILTER()

SUMIFS()

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.