Forum Discussion
Looking for help on what Function to use
Let's say the names are in A2:A100 and D2:D50.
In B2 (or another cell in row 2), enter the formula
=ISERROR(MATCH(A2,$D$2:$D$50))
This will return TRUE if the value of A2 does not occur in B2:B50, FALSE otherwise.
Fill down to row 100.
You can also use this formula in a conditional formatting rule for A2:A100 of type 'Use a formula to determine which cells to format' to highlight the non-matching names.
Hi there,
You may use the following formula to compare two lists (Tables). Sample table is attached:
1 - COUNTIF($A$2:$A$17,B2) & COUNTIF($B$2:$B$17,A2)
2 - =IF(COUNTIF($A$2:$A$17,B2)=0,ROW(B2)) & =IF(COUNTIF($B$2:$B$17,A2)=0,ROW(A2))
3 - =IFERROR(INDEX($B$2:$B$17,SMALL($I$2:$I$19,L2)-1),"") & =IFERROR(INDEX($A$2:$A$17,SMALL($J$2:$J$19,L2)-1),"")
4 - =IFERROR(INDEX($B$2:$B$17,SMALL(IF(COUNTIF($A$2:$A$17,B2:B17)=0,ROW(B2:B17)), L2)-1),"") & =IFERROR(INDEX($A$2:$A$17,SMALL(IF(COUNTIF($B$2:$B$17,A2:A17)=0,ROW(A2:A17)), L2)-1),"") (Ctrl+Shift+Enter)
At the same time, you can do it using Conditional Formatting:
Picture is attached
Please follow the below written link (in Russian) for detailed explanation:
https://www.youtube.com/watch?v=c_ydkYhcXUI