Multiple IF conditions

[Ecel Dumbo]

Hello friends, please help advise a formula

Customer Name Revised Customer Name Desired Answers
Sigma India Pvt Sigma
Sigma New Zealand Sigma
API New Zealand API
API Italy API
Propharma Indonesia Propharma
Propharma London Propharma
Sipla Korea Sipla Korea
Sipla Japan Sipla Japan

5 conditions
If the customer name contains "Sigma", change name to "Sigma"
If Customer name contains "API", change name to "API"
If Customer Name contains "Propharma", change name to "Propharma"
If Customer name contains "JJT", change name to "JJT"
For all other names, same as existing names in Column A

Thanks
Excel Dumbo

 

[Ecel Dumbo]

Sorry data shown above is confusing . Please refer the above attached spreadsheet. I am using Excel 2003

Regards,
Excel Dumbo

 

[Sajan]

Hello,
Here is one approach. Put the following formula in cell B2 and copy down:
=CHOOSE(LOOKUP(5,SEARCH({"*","Sigma","API","Propharma","JJT"}, A2)*{1,2,3,4,5}), A2, "Sigma", "API", "Propharma", "JJT")

Cheers,
Sajan.

 

[Hui]

B2: =IF(OR(LEFT(A2,FIND(" ",A2)-1)="Sigma", LEFT(A2,FIND(" ",A2)-1)="API", LEFT(A2,FIND(" ",A2)-1)="Propharma",LEFT(A2,FIND(" ",A2)-1)="JJT"),LEFT(A2,FIND(" ",A2)-1),A2)
Copy down

 

[Ecel Dumbo]

THanks Heaps Sajan and Hui. Worked well.

 

[Sajan]

Thanks for the feedback! Glad to help! Welcome back any time!