I am attempting to put together a sales tracking spreadsheet for me and other agents in my office. I have completed it and it does everything I want it to do, but I am worried that it will not be user friendly for some of the other agents who are not tech/excel savvy.

I have two columns. one is column C and it contains carrier names you can select from a dropdown list: AFS, AAS, UHC, etc. The other column is D, and it contains the commission rate for each company. currently you have to select the value from a dropdown list (15%, 16%, 18%, etc). I am wanting to make it automatically select the appropriate commission rate based on the information in column C. So that if it detects AFS in C it automatically selects 15%, but if it detects UHC, it automatically selects 18%.

I am familiar with the IF formula but from what I can tell it only works with numbers, and will not recognize words like AFS of UHC.

Any suggestions would be very much appreciated, thank you!

(also is it possible to do the same if there is a date in column C instead of a word?)