# 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

Try to replace in the second VLOOKUP the 4 and the 7 by the IF you stated at the beginning.

Bye

• 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

Hi,

Still unable to get this formula to work - any ideas would be welcomed!

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

All I get is #REF!

• 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

• Re: Tricky Formula

Also, if A24 is formatted as text, but in the lookup it is a number, it won't find it.

Stephen

## Participate now!

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