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!


--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=528990

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


Code:
--------------------
IF(D35110%,"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,))))
--------------------


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(D35110%,"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,))


--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=528990

I would say you should use the following formula type:
=IF(D351.1,"Exceeds Guidelines",IF(D351.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(D351.1,"Exceeds
Guidelines",IF(D351.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


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=528990

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 ,
sharkfoot
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!

Clivey_UK Wrote:
I would say you should use the following formula type:
=IF(D351.1,"Exceeds Guidelines",IF(D351.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(D351.1,"Exceeds
Guidelines",IF(D351.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!


--
sharkfoot
------------------------------------------------------------------------
sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164
View this thread: http://www.excelforum.com/showthread...hreadid=528990

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(D35105%,"A","B")
Then in another cell (say B2) write the next part, i.e.
=IF(D35110%,"Exceeds Guidelines",A2).
Check both formulas work. Now combine them by copying
IF(D35105%,"A","B") from A2 and pasting it to where it says A2 in the
second formula, giving you
IF(D35110%,"Exceeds Guidelines",IF(D35105%,"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

sharkfoot Wrote:
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!


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=528990