Using an IF to combine multiple formulas

How do I write an if formula that does the following:


If D35 is between 0% and 105.00% then return the value of this formula:
=INDEX('setf-lease'!$W$5:$AE$24, MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,), MATCH(D25,'setf-lease'!$W$5:$AE$5,))

If D35 is between 105.01% and 110.00% then return the value of this formula:
=INDEX('setf-lease'!$W$27:$AE$46, MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,), MATCH(D25,'setf-lease'!$W$27:$AE$27,))

If D35 is greater than 110.00% then return the value "Exceeds Guidlines"

So I'm trying to combine more than one formula and do not know how to do that. Can anyone assist? Thanks!

This is what I tried but it doesn't work. Can someone tell me what the problem is?

Please Login or Register  to view this content.

This is driving me crazy.

EDIT

I guess I can post this the regular way since it's so wrong the code thingy doesn't even pick it up:

IF(D35>110%,"Exceeds Guidelines",IF(D35<110%,=INDEX('setf-lease'!$W$5:$AE$24, MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,), MATCH(D25,'setf-lease'!$W$5:$AE$5,)),IF(D35<105%,=INDEX('setf-lease'!$W$27:$AE$46, MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,), MATCH(D25,'setf-lease'!$W$27:$AE$27,))

I would say you should use the following formula type:
=IF(D35>1.1,"Exceeds Guidelines",IF(D35>1.05,"B","A")).
Where "A" represents your first condition, "B" your second, and anything over 1.1 (110%) gets 'Exceeds Guidelines.
Therefore the full formula should be:
=IF(D35>1.1,"Exceeds Guidelines",IF(D35>1.05,INDEX('setf-lease'!$W$27:$AE$46, MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,), MATCH(D25,'setf-lease'!$W$27:$AE$27,)),INDEX('setf-lease'!$W$5:$AE$24, MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,), MATCH(D25,'setf-lease'!$W$5:$AE$5,))))
I can't test out without the values, but I've simply pasted in your conditions in place of A and B.
Clive

Originally Posted by Clivey_UK

I would say you should use the following formula type:
=IF(D35>1.1,"Exceeds Guidelines",IF(D35>1.05,"B","A")).
Where "A" represents your first condition, "B" your second, and anything over 1.1 (110%) gets 'Exceeds Guidelines.
Therefore the full formula should be:
=IF(D35>1.1,"Exceeds Guidelines",IF(D35>1.05,INDEX('setf-lease'!$W$27:$AE$46, MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,), MATCH(D25,'setf-lease'!$W$27:$AE$27,)),INDEX('setf-lease'!$W$5:$AE$24, MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,), MATCH(D25,'setf-lease'!$W$5:$AE$5,))))
I can't test out without the values, but I've simply pasted in your conditions in place of A and B.
Clive

That did it. My brain starts turning to mush after a couple of hours of tinkering with the same formula. I appreciate your assistance and thank you!

Try...

=IF(D35<=110%,VLOOKUP('TNT'!O4,IF(D35<=105%,'setf-lease'!$W$5:$AE$24,'set
f-lease'!$W$27:$AE$46),MATCH(D25,IF(D35<=105%,'setf-lease'!$W$5:$AE$5,'se
tf-lease'!$W$27:$AE$27),0),0),"Exceeds Guidelines")

Hope this helps!

In article <[email protected]>,
sharkfoot <[email protected]>
wrote:

> How do I write an if formula that does the following:
>
>
> If D35 is between 0% and 105.00% then return the value of this
> formula:
> =INDEX('setf-lease'!$W$5:$AE$24,
> MATCH('TNT'!O4,'setf-lease'!$W$5:$W$24,),
> MATCH(D25,'setf-lease'!$W$5:$AE$5,))
>
> If D35 is between 105.01% and 110.00% then return the value of this
> formula:
> =INDEX('setf-lease'!$W$27:$AE$46,
> MATCH('TNT'!O4,'setf-lease'!$W$27:$W$46,),
> MATCH(D25,'setf-lease'!$W$27:$AE$27,))
>
> If D35 is greater than 110.00% then return the value "Exceeds
> Guidlines"
>
> So I'm trying to combine more than one formula and do not know how to
> do that. Can anyone assist? Thanks!

Domenic's response looks like it might be a neater formula. Give it a try.
I find the best way to do complex formulas (I also get the mush problem!) is to build it up in different cells. You'd already created the most complex part so keep that to one side.
Then in another cell (say A2 for sake of argument), do a simple IF formula for just 2 of the conditions. e.g.
=IF(D35>105%,"A","B")
Then in another cell (say B2) write the next part, i.e.
=IF(D35>110%,"Exceeds Guidelines",A2).
Check both formulas work. Now combine them by copying IF(D35>105%,"A","B") from A2 and pasting it to where it says A2 in the second formula, giving you
IF(D35>110%,"Exceeds Guidelines",IF(D35>105%,"A","B"))
AFter checking the combined formula works, replace "A" and "B" with the bits of the formula you'd already worked out.
It's important to drop the initial '=' when you copy the formulas into another formula.
Clive

Originally Posted by sharkfoot

That did it. My brain starts turning to mush after a couple of hours of tinkering with the same formula. I appreciate your assistance and thank you!