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.
Unless I'm mistaken, to get exactly what you're asking for, you're going to need Power Query or a tool like that; sadly, I'm not experienced with that.
However, I am experienced with databases and database design, and I'm questioning why you have three separate sheets for the subordinate data here. One could suffice, I believe, recognizing that column B in those sheets is redundant with the name affixed to the tab. Just let that column do the differentiating that the tab is doing.
(An aside: we often create separate pages because that's how we'd do it on paper, but the reality is that such a layout actually (often) interferes with Excel's marvelous abilities to parse such distinct entities in a single database or table, just using a column to differentiate what was originally presented as separate tabs.)
When you do that--as I've done in the demo attached--then a simple FILTER function, nested in TRANSPOSE, gives you the desired result, albeit in three separate columns, G, H and I.
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.
