Marking if one of a pair included in list

Hi,

Assuming your IDs will always be sorted, in D2 and copy down:

=IF(SUMPRODUCT(0+($A$2:$A$15=A2),0+(ISNUMBER(MATCH($B$2:$B$15,$G$2:$G$4,0)))),1,"")

Regards

In C2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IF(SUM(COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,TRANSPOSE($F$2:$F$4))),1,"")

Drag it down...

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Or no sorting needed: array formula (Ctrl+Shift+Enter comitted)

Please Login or Register  to view this content.

see attachment

Oops... Here is the revised sotluion

Non Array Formula

=IF(SUMPRODUCT(COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,$F$2:$F$4)),1,"")

@Sixth

OP states 2003

Originally Posted by XOR LX

@Sixth
OP states 2003

But the attached sample file is 2007 lol

Yeah, I guess I had to update the Excel Info :p...

Thanks for the solutions!

Yeh, I was also thinking it is for Ex2003 while obviously file extension was xlsx - blind me lol

@Sixthsense: We have an open conversation on PM, but your private mesages has exceeded quota so I cannot send answer

Originally Posted by Kaper

your private mesages has exceeded quota so I cannot send answer

Oopss.. Sorry for that... now it's having more space since I deleted the old messages

Hope moderators will be tollerant (because it gets really OT), but: nope, it didn't work. Still getting:

The following errors occurred with your submission
Sixthsense has exceeded their stored private messages quota and cannot accept further messages until they clear some space.