Tricky Formula

    Hi Everyone!


    Been using excel for years now, and am trying to get an if formula to work based on a condition, whereby it then processes one of two possible formulas. I've changed the syntax a little on this post to give you an idea of what I'm trying to acheive, basically depending on the value, a different formula is used. Can't seem to get the syntax right? Any ideas?


    =IF(F15<4,use formula a, otherwise use formula b)


    formula a)
    IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,4,FALSE))+(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE)),””)


    formula b)
    IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,7,FALSE))+(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE,””)))))))


    Rgds,
    B

    Re: Tricky Formula


    rlavoie


    the vlookups point to either 4 or 7 as they produce a different result depending on what the value of F15 is - I'm not sure if what you've suggested will work, but I'll try it and see if it helps.


    Thanks for the post


    B

    Re: Tricky Formula


    Try this.


    Code
    =IF(F15<4,IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,4,FALSE))+(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE)),""),IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,7,FALSE))+(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE)),""))


    HTH

    Re: Tricky Formula


    Hi


    You don't say whether you can't get an answer, or whether the answer you get is wrong?


    I got this formula to work ok - it produces answers, but did notice that the ranges in the lookups aren't all the same: some have INDEX!$A$4:$E$729, while some have INDEX!$A$5:$E$729. Have you included headings in one and not the other, perhaps?


    If the item from cell A24 you are looking up is in the first row of the range on INDEX, then it wont find it.


    Check that and see if it works.


    Here's what worked for me:
    =IF(L15<4,
    IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,4,FALSE))
    +(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE)),""),
    IF(B24>0,($H$17*1.564)*(VLOOKUP(A24,INDEX!$A$5:$E$729,3,FALSE))*(VLOOKUP(A24,INDEX!$A$4:$E$729,7,FALSE))
    +(VLOOKUP(A24,INDEX!$A$5:$E$729,5,FALSE)),""))


    Also, you have two ways of getting "". Does this matter?


    Cheers


    Stephen

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login