Excel - if statement based on a list of conditions

I'm trying to set up an if statement containing conditions that I've written out in a list. When the user selects options from a dropdown, I'd like the "total cost" cell to be populated.

For example, if the user selects:

Product 1: Brand A
Product 2: Brand B
Total cost: 200

Is there a better way to do this, than write out 9 nested if statements?

Solution 1:

You can use an Index/Match formula.

Cell F4 formula:

=INDEX(C2:C10,MATCH(1,(F2=A2:A10)*(F3=B2:B10),0))

After entering the formula, press CTRL-SHFT-ENTR to make this an array formula (to get the curly brackets).

Solution 2:

You may also use Sumproduct function.

Such as in F4, enter formula, =SUMPRODUCT((A2:A10=F2)*(B2:B10=F3)*(C2:C10)).