# IF function too short, what do I do now?

#### mj1000

##### New Member
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,))))))))))))

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Scott Huish

##### MrExcel MVP
Hi, welcome to the board!

Can you explain what all this is supposed to do?

#### Brian from Maui

##### MrExcel MVP
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
Try this:

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

#### mj1000

##### New Member
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
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
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)))
--------------------------------------------------------------------------------

Thanks sooo much

#### NickPaton

##### New Member
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
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.

#### NickPaton

##### New Member
Hi

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

In the meantime, good luck!

Replies
1
Views
223
Replies
7
Views
983
Replies
7
Views
623
Replies
0
Views
258
Replies
5
Views
919

1,181,614
Messages
5,930,931
Members
436,767
Latest member
Langaws

### 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.

### Which adblocker are you using?

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

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