Forum Discussion
Solved
Intersection of two lookup values
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
- 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)).
=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.
