Need help with IF equation.

[aaron061883]

I have a drop down for 3 service companies and they have different chemicals that all do the same thing so I have to have the right trade name for the chemical used based upon the service company.
Service company will be selected from drop down D11
I have a list of chemical names (##) for each company.
If you have company A the chemical will auto select from AB22:AD31
The equation needs to be M22-M31 the generic chemical name is P22:P31

Any help with this equation would be greatly appreciated.


I will attach file in a second

[aaron061883]

Attachment included

[aaron061883]

I know its possible with a few If statements I just know how it do it

[Phlegon_of_Tralles]

I'm not 100% sure of what you are doing, but I wrote this:

=IF(D11="A",INDIRECT(CONCATENATE("AB",RANDBETWEEN(22,31))),IF(D11="B",INDIRECT(CONCATENATE("AC",RANDBETWEEN(22,31))),IF(D11="C",INDIRECT(CONCATENATE("AD",RANDBETWEEN(22,31))),"ERROR")))