IF function too short, what do I do now?

mj1000

New Member
Joined
Jan 20, 2005
Messages
24
Please help. How do I work this formula into a LOOKUP function. I need 12 nested IF functions and of course I am only allowed 7. Thanks in advance :biggrin:

=IF(AND(C4<=4,C5="fr",C6="n"),'Rate Sheet'!C4,IF(AND(C4<=7,C5="fr",C6="n"),'Rate Sheet'!D4,IF(AND(C4<=10,C5="fr",C6="n"),'Rate Sheet'!E4,IF(AND(C4<=4,C5="fr",C6="y"),'Rate Sheet'!C5,IF(AND(C4<=7,C5="fr",C6="y"),'Rate Sheet'!D5,IF(AND(C4<=10,C5="fr",C6="y"),'Rate Sheet'!E5,IF(AND(C4<=4,C5="nc",C6="n"),'Rate Sheet'!C7,IF(AND(C4<=7,C5="nc",C6="n"),'Rate Sheet'!D7,IF(AND(C4<=10,C5="nc",C6="n"),'Rate Sheet'!E7,IF(AND(C4<=4,C5="nc",C6="y"),'Rate Sheet'!C8,IF(AND(C4<=7,C5="nc",C6="y"),'Rate Sheet'!D8,IF(AND(C4<=10,C5="nc",C6="y"),'Rate Sheet'!E8,))))))))))))
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Can you explain what all this is supposed to do?
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
I'm sure there's a better, easier way, but...

construct a table with the combinations,concatenate and use Index/Match
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Try this:

Code:
=OFFSET('Rate Sheet'!C3,4*(C5="nc")+(C6="y"),IF(C4<=4,0,IF(C4<=7,1,2)))
 

mj1000

New Member
Joined
Jan 20, 2005
Messages
24

ADVERTISEMENT

Thanks for the welcome.

I have created a table that has 12 different values. Each value is a result of 3 criteria. So, if cell A1 = 1, B1 = 2 and C1 = 3 then return the value in D1. If cell A1 = 2, B1 = 3 and C1 = 4 then return the value in D2 and so on.
I am trying to teach myself the LOOKUP function and it is confusing me. I do not know if it is possible to use three criteria before returning a value. Sorry if I am not making my problem clear enough for you to help me.
 

mj1000

New Member
Joined
Jan 20, 2005
Messages
24
Thanks for the formula Seti, I see how this would be able to work however, I do not know enough about this to be able to fill in the blanks. Are you able to break down the formula and let me know what it means?? Please??
 

mj1000

New Member
Joined
Jan 20, 2005
Messages
24

ADVERTISEMENT

OFFSET not quote right.

This worked, however it is not returning the first three values in my (very long) IF statement above

code:
--------------------------------------------------------------------------------
=OFFSET('Rate Sheet'!C3,4*(C5="nc")+(C6="y"),IF(C4<=4,0,IF(C4<=7,1,2)))
--------------------------------------------------------------------------------

Can anyone please tell me what changes need to be made?

Thanks sooo much
 

NickPaton

New Member
Joined
Oct 31, 2002
Messages
34
To use more than 7 If Statements, when you have completed the 7th you replace the 'else' result with the next cell value, and then continue with the if statements in the next cell i.e:

Assuming the whole formula is written in cell A1.

Your 7th statement is ".....IF(AND(C4<=4,C5="nc",C6="n"),'Rate Sheet'!C7.....".

Replace "C7"with "A2" or a convenient cell value, and continue with the rest of the formula in cell A2. Doing this you can continue on for ever, and the resultant value will be recorded in A1.

If you don't have enough spare cells in the immediate vicinity, I suggest doing it to one side of the viewable area, say in cells CA1, CA2 or whatever, and merely doing an "=CA1" in cell A1 to read the value.

It is better though, to use one of the other shorter methods, but this will work for you, but is more clunkier!
 

mj1000

New Member
Joined
Jan 20, 2005
Messages
24
Thanks Nick, Would it be possible for you to add this to the existing OFFSET formula, I can not for the life of me, get it to work.

Thanks for your help.
 

NickPaton

New Member
Joined
Oct 31, 2002
Messages
34
Hi

I'm not familiar with OFFSET, but can have a look into it tomorrow sometime and reply back.

In the meantime, good luck!
 

Forum statistics

Threads
1,147,621
Messages
5,742,184
Members
423,710
Latest member
Duarte85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top