Search code examples
excelexcel-formulaexcel-2007

Excel formula for searching two text in one cell and return values based on the result

I am looking for an excel formula for searching two text(dba,BP) in one cell and return value if dba found then return dba and if BP found then return BP. I have used below formula but its only returning one value.

=IF(OR(ISNUMBER(SEARCH("dba,A25)),ISNUMBER(SEARCH("bpi",A25))),"dba","")

Solution

  • You can put another IF inside first IF, so that it works like if first case not match, it will test for second case

    =IF(<first case>, <first case matched> ,IF(<second case>, <second case matched>, <no matches>))

    In your case,

    =IF(ISNUMBER(SEARCH("dba",A1)),"dba",IF(ISNUMBER(SEARCH("bpi",A1)),"bpi",""))