Compare Four Columns and Return Label-CodePudding

I'm trying to compare membership rosters which contain 2 columns each for first name and last name. If the names match I want to add conditional formatting and a value in a 5th column. (I'm trying to determine who is a member and who is an external contact.)

I've tried to adapt some formulas found here but no luck.

For example:

If Jon and Doe exists in A & B and C & D on any row as long as Jon and Doe are together in both columns, than I would like to add conditional formatting to Jon and Doe in column A & B and add the word "Member" in a separate column. If Jon Doe only exists in columns A and B, then add the word "External" instead of Member. My example image can explain better.

I got this close but am not able to adapt it to use labels much less conditional formatting. I'm happy to ditch conditional formatting if it's too complex.

=INDEX($N:$N,MATCH(1,(A2=$L:$L)*(B2=$M:$M),1))

CodePudding user response：

Conditional Format Formula, applied to A2:B4 in the sample data

=IFERROR(MATCH(1,($A2=$C:$C)*($B2=$D:$D),0)>0,FALSE)

Status Formula

=IFERROR(IF(MATCH(1,($A2=$C:$C)*($B2=$D:$D),0)>0,"Member",""),"External")

_{Note the $'s anchoring A and B columns}