Too many Nested IF Statements... Alternative?

Hi Everyone,


I have 7 different products with their own item code. I am currently using nested If statements to automatically put in the appropriate code based on the name of the product. If the function finds "product name", it will output "product code".

The problem is that it becomes a little hard to keep track of the if statements, and I feel that Excel must have a more efficient way to do this... I tried Index Match so that it would look up the products from an array, but that is not working for me, because "product name" is different from "product code", but Index Match only returns "product name".

The list of products I currently have is only 2... I haven't even entered the rest because it was getting so long...

- CRM Anywhere
- CRM Anywhere Pro

Here is the process I am trying to achieve... I download a report... the report shows the product names as shown above... I want excel to look for the keyword "Anywhere", and then if that's true, determine if it's just Anywhere, or if it's Anywhere Pro. Then, once it determines which one it is from a string of text that varies, I want it to output the appropriate product code.

Below is the code that I am entering for JUST these two products... I still need to enter more products:

=IF(LEN(Source_Data!E4)>0,IF(ISNUMBER(SEARCH("Anywhere",Source_Data!E4)),IF(ISNUMBER(SEARCH("Pro",Source_Data!E4)),"CRM ANYWHERE PRO","CRM ANYWHERE"),""),"")

Any help would be greatly appreciated!

Post a sample file (not image) showing examples of expected outcomes.

To post a file, click "Go Advanced" then scroll down to "Manage Attachments"

I think I attached the workbook... please let me know if you do not see it, and thanks again!

Thanks for the replies! I guess I'm going with the VLOOKUP, or Index(Match()) functions. It turns out this is working way better than using endless IF Statements lol.