To make things easier give the data in column 1 a
Name e.g. "List".
First part of your calculation then needs to do a
VLOOKUP() on that list, looking for an exact match.
VLOOKUP returns #N/A if it can't find the data so you will also need a function called
ISNA() which returns TRUE if a cell contains, or a function returns, #N/A
Because you actually want to list the ones that don't match you'll also need the function
NOT() to reverse the effects of the lookup.
Finally put all of this together into an
IF() function in your formula returning a blank for the true part and the item from column 2 in the false part
[Edit - worked example]
I have your column 1 values in "Column A" and column 2 values in "Column B". I've used
Insert, Name, Define to call all the data in Column B
List. I then sorted column A and column B just to make it easier to view the results
First part of calculation is
=VLOOKUP(A1, List, 1, FALSE)
which returns results
Eglise Eglise Eglise
EndacottEndacot #N/A
Fido Fido Fido
Green Green Green
Hibbard Hibbard Hibbard
Olney Olnei #N/A
Scott Scott Scott
White #N/A
If I wrap that in the ISNA() function and then in the NOT() function ...
=NOT(ISNA(VLOOKUP(A1, List, 1, FALSE)))
then I get results
Eglise Eglise TRUE
EndacottEndacot FALSE
Fido Fido TRUE
Green Green TRUE
Hibbard Hibbard TRUE
Olney Olnei FALSE
Scott Scott TRUE
White FALSE
Finally, put that into an IF() statement to tidy up the output ...
=IF(NOT(ISNA(VLOOKUP(A1, List, 1, FALSE))), "", B1)
Which gives results
Eglise Eglise
EndacottEndacot Endacot
Fido Fido
Green Green
Hibbard Hibbard
Olney Olnei Olnei
Scott Scott
White White
Which is very close to what you said your expected results were. I suspect you have a typing error in there as you appear to be trying to merge results from two columns. However this worked example should be enough to set you down the right path
Submit an article or tip