Need Help fixing a formula

[mj1000]

I had this and because the if only allows 7 nested statements:
=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,))))))))))))

Seti helped be by giving me this (thanks again for that):
=OFFSET('Rate Sheet'!C3,4*(C5="nc")+(C6="y"),IF(C4<=4,0,IF(C4<=7,1,2)))

This statement returns 9 out of the possible 12 and I have been unsuccessful it getting it to return 12 out of 12. It is returning all but these three values:
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,

Is anyone able to please help me fix the OFFSET statement?

Thanks tons

 

[Seti]

OK, this is fully tested and works on my setup. Hope it works for you too.

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

 

[mj1000]

Not Working for Me

Thanks for your help Seti, but it’s not working for me, it is returning whatever number I enter in Cell C4.

What I would like to do is enter the following information a worksheet named ‘Premium’

Cell C4 will = 1-4, 5-7 or 8-10
Cell C5 will = fr or nc
Cell C6 will = y or n

If I enter any number between 1-4, fr and y
I would like it to return the value in the cell C4 located on the ‘Rate Sheet’

5-7, fr and y
return D4

8-10, fr and y
return E4

1-4, fr and n
return C5

5-7, fr and n
return D5

8-10, fr and n
return E5

1-4, nc and y
return C7

5-7, nc and y
return D7

8-10, nc and y
return E7

1-4, nc and n
return C8

5-7, nc and n
return D8

8-10, nc and n
return E8

Thanks again, I think I need to take and Excel for Dummies course.

 

[mj1000]

One more thing

The formula

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

worked except it would not return the values in cells C6, D6, E6.

 

[Aladin Akyurek]

Re: Not Working for Me

Thanks for your help Seti, but it’s not working for me, it is returning whatever number I enter in Cell C4.

What I would like to do is enter the following information a worksheet named ‘Premium’

Cell C4 will = 1-4, 5-7 or 8-10
Cell C5 will = fr or nc
Cell C6 will = y or n

If I enter any number between 1-4, fr and y
I would like it to return the value in the cell C4 located on the ‘Rate Sheet’

5-7, fr and y
return D4

8-10, fr and y
return E4

1-4, fr and n
return C5

5-7, fr and n
return D5

8-10, fr and n
return E5

1-4, nc and y
return C7

5-7, nc and y
return D7

8-10, nc and y
return E7

1-4, nc and n
return C8

5-7, nc and n
return D8

8-10, nc and n
return E8

Thanks again, I think I need to take and Excel for Dummies course.

Does this pick up the right rate?...

=INDEX('Rate Sheet'!$C$4:$E$8,VLOOKUP(C5&","&C6,{"fr,y",1;"fr,n",2;"nc,y",4;"nc,n",5},2,0),MATCH(C4,{1,5,8},1))

 

[mj1000]

YES!! THank you thank you!! I can even read the formula, thank you thank you!!