Multiple conditions formula

[jonhat]

I'm trying to write a formula that will allow me to select 2 conditions from drop-down lists in 2 columns, and then based on the two options selected, select a correpsonding cell. THis cell is then to be multiplied by it's adjacent cell to give a result.
At the moment I've tried to do this with a multiple "IF AND" formula but I can't work out whether that is even viable to be honest! This is what I've written and it returns an error statement, however I'm struggling to understand why that is:

=IF(AND(G3="Blue",H3="Square"),(I3*C$41),IF(AND(G3="Blue",H3="Circle"),(I3*C$47)),IF(AND(G3="Green",H3="Square"),(I3*C$42),IF(AND(G3="Green",H3="Circle"),(I3*C$48)),IF(AND(G3="Red",H3="Square"),(I3*C$43),IF(AND(G3="Red",H3="Circle"),(I3*C$49)),IF(AND(G3="Yellow",H3="Square"),(I3*C$44),IF(AND(G3="Yellow",H3="Circle"),(I3*C$50)),IF(AND(G3="Orange",H3="Square"),(I3*C$45),IF(AND(G3="Orange",H3="Circle"),(I3*C$51)))

In the example above, I have 2 drop-down lists, one for colours (Blue, Green, Red, Yellow and Orange) and one for shapes (Square and Circle). So if in column 1 I select "Green" and in column 2 I select "Circle", I want it to add the result from cell C48 and then multiply it by the adjacent cell in column I.

I suspect that there's probably a much easier & 'cleaner' way of presenting these results, but it's slightly beyond my current knowledge. Any help that anyone can offer would be fantastic & much appreciated.

Thanks,
Jon

[FDibbins]

1. Always better to upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
2. looking at your formula quickly (more to follow), looks like you may be closing your 2nd IF too soon?

[FDibbins]

Quick way to check for opening/closing brackets...
=IF(AND(G3="Blue",H3="Square"),(I3*C$41)
,IF(AND(G3="Blue",H3="Circle"),(I3*C$47))
,IF(AND(G3="Green",H3="Square"),(I3*C$42)
,IF(AND(G3="Green",H3="Circle"),(I3*C$48))
,IF(AND(G3="Red",H3="Square"),(I3*C$43),
IF(AND(G3="Red",H3="Circle"),(I3*C$49))
,IF(AND(G3="Yellow",H3="Square"),(I3*C$44)
,IF(AND(G3="Yellow",H3="Circle"),(I3*C$50))
,IF(AND(G3="Orange",H3="Square"),(I3*C$45)
,IF(AND(G3="Orange",H3="Circle"),(I3*C$51)))

A quick look shows that you have closed the BOLDED ifs, you can tell ny the 2 )) at the end of the row

[FDibbins]

hmm also, looks like you could probably use INDEX/MATCH for this, but a sample WB will offer better suggestions

[jonhat]

Quick way to check for opening/closing brackets...
=IF(AND(G3="Blue",H3="Square"),(I3*C$41)
,IF(AND(G3="Blue",H3="Circle"),(I3*C$47))
,IF(AND(G3="Green",H3="Square"),(I3*C$42)
,IF(AND(G3="Green",H3="Circle"),(I3*C$48))
,IF(AND(G3="Red",H3="Square"),(I3*C$43),
IF(AND(G3="Red",H3="Circle"),(I3*C$49))
,IF(AND(G3="Yellow",H3="Square"),(I3*C$44)
,IF(AND(G3="Yellow",H3="Circle"),(I3*C$50))
,IF(AND(G3="Orange",H3="Square"),(I3*C$45)
,IF(AND(G3="Orange",H3="Circle"),(I3*C$51)))

A quick look shows that you have closed the BOLDED ifs, you can tell ny the 2 )) at the end of the row

That worked great, thank you very much for your help. Noting your point 1 in your first email, I will ensure that I do that in future. Thanks again.

[FDibbins]

Thanks for the feedback. Did you get this all resolved?