Forum Discussion
Changing a cell's formula based on the value of another cell
I need to make a cell on my worksheet (U9) reflect a different formula based on the name from a drop down list in cell D9 (example, If D9 is "Bob", I want U9 to calculate "F9*.4", but if it's anyone else I want it to calculate "F9*.25" (Different commission levels). I tried =IF(D9="Bob","=F9*.4","=F9*.15"), but it only displays the formula, not the value in U9. Any suggestions?
Hi Fred,
=F9*IF(D9="Bob",0.4,0.25)
- That was it! Thank you very much! Now to complicate things a bit further, If two different Salespeople have two different commission levels, but everyone else is at a third level, how would I do that? Example: Steve is at 25%, Bob is at 40% and everyone else is at 15%
Fred,
That could be nested IF like
=F9*IF(D9="Bob",0.4,IF(D9="Steve",0.25,0.15))
but more flexible, especially if you have bigger list of names, create table/range with list of names and commission level for each of them, like
and find the level against the name like
=F9*IFERROR(INDEX($A$1:$B$2,MATCH(D9,$A$1:$A$2,0),2),0.15)
How INDEX/MATCH works see, for eample https://exceljet.net/formula/basic-index-match-exact or google it
That's great! Thanks very much.
