Forum Discussion

Jamie Swan's avatar
Jamie Swan
Copper Contributor
Apr 06, 2018

Excel Formula Help

Hoping someone can help.  I'm trying to write a formula for the following example:

Cell A1 will have A,B,or C entered.

Cell D1 will have a number (let's say 5)

Cell E1 formula: if A1 is A then D1 *3, if A1 is B then D1 * 4, if A1 is C then D1 *5

I keep getting error messages when trying to write the formula.  I can get it to work for the first part (if A1 is A then D1 * 3) but when I add the other two parts I get errors.

Excel Desktop
Formulas & Functions

3 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 06, 2018

    Hi Jamie,

     

    This is the correct syntax of the formula which is called (Nested IF):

    =IF(A1="A",D1*3,IF(A1="B",D1*4,IF(A1="C",D1*5,0)))

     

    NOTE: If the cell A1 doesn't contain A, B, or C, the formula will return 0.

     

    Hope that helps

    Haytham

     

    • Jamie Swan's avatar
      Jamie Swan
      Copper Contributor
      Apr 06, 2018

      Thank You! I was making the formula more complex than it needed to be.  Thanks Again!

      • Jamil's avatar
        Jamil
        Bronze Contributor
        Apr 06, 2018
        While Haytham solution works for you.

        here are another two ways you can achieve the same result. simplified =D1*IF(A1="A",3,IF(A1="B",4,IF(A1="C",5,0)))

        and with VLOOKUP

        =D1*IFERROR(VLOOKUP(A1,{"A",3;"B",4;"C",5},2,0),0)