How to search a column value in another column in excel

Question

I want search cell A value in cell B and arrange accordingly in cell C.

How to achieve this?

For exmaple A1= abcd xyz and B10 = abcd so B10 is exist in A1 so I want to arrange this B10 value in C1

Answer 1

You can type below formula in Cell C1. If B10 is found in A1, it will put B10 value in C1. Otherwise, it will put "".

=IF(SEARCH(B10,A1) > 0,B10,"")

UPDATE : If value is not present, it will lead to #VALUE error. You can try below solution

=IFERROR(IF(SEARCH(B1,A1),B1,""),"")

@Harun24HR, has provided more cleaner solution to the problem.

Answer 2

Try below formula.

 =IF(ISNUMBER(SEARCH(B1,A1)),B1,"")