I have a worksheet where two columns contains name. I need to check that if one name is appering in any of these two columns then result shoule be shown in third column or if that name is not there then third column should be blank.

If We have to find KEVIN MERRITT then result should be like this

Name 1 Name 2 Result
Mr ANDREW OSBORN Mr KEVIN MERRITT KEVIN MERRITT
Mr NEIL CALDWELL Mr MOHAMMED KHAN
Mr KEVIN MERRITT Mr NEIL CALDWELL KEVIN MERRITT
Mr MOHAMMED KHAN Miss HELEN JOHNSON
Mr KEVIN MERRITT Mr NEIL CALDWELL KEVIN MERRITT
Miss HELEN JOHNSON Mr MOHAMMED KHAN
Mr ANDREW OSBORN Miss HELEN JOHNSON
Mr NEIL CALDWELL Miss HELEN JOHNSON
Mr KEVIN MERRITT Mr MOHAMMED KHAN KEVIN MERRITT
Mr MOHAMMED KHAN Mr KEVIN MERRITT KEVIN MERRITT
Mr KEVIN MERRITT Miss HELEN JOHNSON KEVIN MERRITT

Column A = Name 1
Column B = Name 2
Column C = Result

List starts in row 2.

The formula you put in cell C2 is:
=IF(OR(A1="Mr Kevin Merritt",B1="Mr Kevin Merritt"),"KEVIN MERRITT","")

Now copy that formula down to C3... etc.

nikeshtnt agrees: You are too goodSO that's going to work for you?

The only downside to this method is that it requires the spelling of "Mr Kevin Merritt" to be exactly correct in one of the two columns to get the KEVIN MERRITT to appear in the third.

I know there's a way to watch for "part" of the text in a cell, but my head hurts when I start to think about it.

Good luck with your sheet. Come back if you get stuck again.