assigning a value in a cell based on your selection from a drop down menu

hey ya'll

i have a ten item drop down menu in Column A. Each item in this drop down menu represents the name of a company i do business with. I have created this drop down menu as "CUSTOMERS" on a separate tab using "define name". They run from A1-A10 on this tab.

Each of these customers has their own commission rate (or percentage) assigned to to them. I have used "define name" to call these "RATES". They run from B1-B10 on the tab as "CUSTOMERS"

I have a $value assigned in Column B where I can assign my invoice amount in dollars & cents.

Column C is where I would be doing my algebra to determine what our commission percentage of each invoice is.


As an example, "Customer A" would be Bill Johnson and he has a commission rate of 6.5%. And the invoice is for $1,800.00. I'd like to get it so that by selecting "Bill Johnson" from the drop down menu in Column A, that it automatically applies the 6.5% rate to the formula for Column C.

In this case it look something like (and be advised i'm using rudimentary logis here & have little to no experience in how Excel formulas work)

IF A1 = (CUSTOMERS "A2") then C1 = B1*(RATES "B2")

or if CUSTOMERS = Bill Johnson, then $117.00 = $1,800 * 6.5%


I feel like i'm 90% there but just need a little help in getting over the final hump



any help would be greatly appreciated as this is the last manual thing i current have yet to figure out for what i need this spreadsheet to do


Sincerely,

TMS

example attached