IF function too short, what do I do now?

[mj1000]

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

=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,))))))))))))

 

[Scott Huish]

Hi, welcome to the board!

Can you explain what all this is supposed to do?

 

[Brian from Maui]

I'm sure there's a better, easier way, but...

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

 

[Seti]

Try this:

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

 

[mj1000]

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]

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]

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]

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]

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]

Hi

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

In the meantime, good luck!