Forum Discussion
Solved
Excel: Combining IF and duplicate value
Hello everyone, First of all, I'm just starting with excel so my question will probably look very stupid or basic, and, in advance I apologize, but I'm really struggling and this excel w...
As a comment, that could be done without helper columns with formula rule for columns A and B as
=SUMPRODUCT(--ISNUMBER(MATCH($A1&$B1,$D$1:$D$100&$E$1:$E$100,0)))*NOT(ISBLANK($A1))
and similar for Team 2
Personally I would up the technology level to achieved improved functionality. Both Team1 and Team2 data could be turned into tables in order to make all references dynamic. The highlighting formulas may be developed using worksheet cells but ultimately Name Manager should be used to provide names for the formulas. Thus, 'highlight1' (used for the table Team1) should refer to
= COUNTIFS(Team2[A],Team1[@A],Team2[B],Team1[@B])
whilst the corresponding 'highlight2' would be
= COUNTIFS(Team1[A],Team2[@A],Team1[B],Team2[@B])
These named formulae are then used as the basis for conditional formatting.