# How to write an IF formula for a multiple choices?

I have one drop down cell that contains choices of "A Company", "B Company". The next cell has a drop down with choices of "Founder", "Director", "Lead". I'm trying to right a formula to calculate the following:

If cell 1 is "A Company" and next "Founder" then I want the cell to reflect the result of "220"
If cell 1 is "A Company" and next is "Director" then I want the cell to reflect result of "200*.9"
If cell 1 is "A Company" and next is "Lead" than I want the cell to reflect result of "200*.7"
If cell 1 is "B Company" and next is "Founder" then I want the cell to reflect the result of "175"
If cell 1 is "B Company" and next is "Director" then I want the cell to reflect result of "175*.9"
and so forth
Is it possible to create a formula that will do that?

• I suspect that your real sheet doesn't contain just two companies, and that you likely want to process more than one row of such data. But it's not efficient to write complex formulas around guesswork. If you want to share a link to the spreadsheet (or to a copy of it, or to a realistic mock-up of it setup the same way and with enough limited, sanitized data to fully illustrate the goal, I'm take another look. Apr 5, 2022 at 16:02
• Of course Erik is right, it would be much better to create a second table with the various values ​​and then get them from there Apr 5, 2022 at 16:18

## 1 Answer

if the two drop-down cells are A2 and B2:

``````=Ifs(
or( A2="", B2=""),"",
and(A2="A Company",B2="Founder"),220,
and(A2="A Company",B2="Director"),220*0.9,
and(A2="A Company",B2="Lead"),220*0.7,
and(A2="B Company",B2="Founder"),175,
and(A2="B Company",B2="Director"),175*0.9,
and(A2="B Company",B2="Lead"),175*0.7
)
``````