Assuming your left-most "Col1" header is in cell A1
, select cells E2:F6
(with E2
as the Active Cell), and use this formula for Conditional Formatting:
=COUNTIFS($A:$A,$E2,$B:$B,$F2)=0
Result:
List from User1 in Column A, List from User2 in Column B, in Column C, start in C2 write the following formula:
=IFERROR(INDEX($B$2:$B$10,SMALL(IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,ROW($B$2:$B$10)-1,9999999999),ROW()-1)),"")
Array Formula press Ctrl+Shift+Enter at the same time
Drag the formula down it will return the ID if matching (the matching will follow and later the empty) or empty
A2:A10 is the Data in column A without the header,(List user1) change it to your Data references
B2:B10 is the Data in column B without header,(List user2) change it to correspond your Data references
Keep the $ for fixed references
Countif will find the matching ID
If will return the Row number when matching
Index will return the ID corresponding to the smallest row() when dragging the formula
Update corresponding to your references
=IFERROR(INDEX($E$5:$E$898,SMALL(IF(COUNTIF($C$5:$C$247,$E$5:$E$898)>0,ROW($E$5:$E$898)-4,999999999),ROW(A5)-4)),"")
Because your data start in row 5, I assume your formula will start also in row 5
I changed the formula to correspond to this
=IFERROR(INDEX($E$5:$E$898,SMALL(IF(COUNTIF($C$5:$C$247,$E$5:$E$898)>0,ROW($E$5:$E$898)-4,999999999),ROW(A5)-4)),"")
Best Answer
If it is ok to have another row - which wil match row 2 - you can use
HLOOKUP
as shown in the image below.