Joining nested IF functions

Dear All

I am aiming to classify Column E as 'Type' 1-7 on the basis of values in columns D and C. If values in D are <80 then I need to base the type (column E) on values in column C. So for example, if column D is <80 and column C is>=10, <50 then the type value in column E should be '2'. I have produced a formula for this which works OK:

=IF(D2<80,IF(C2<10,"1",IF(AND(C2>=10,C2<50),"2",IF(AND(C2>=50,C2<100),"3",IF(AND(C2>=100,C2<200),"5",IF(C2>=200,"7"))))))

I also need another formula for when column D is >80. A similar situation applied but with slightly different types in column E based on values in column C. I have produced this formula which works OK:

=IF(D2>80,IF(C2<10,"1",IF(AND(C2>=10,C2<50),"2",IF(AND(C2>=50,C2<100),"4",IF(AND(C2>=100,C2<200),"6",IF(C2>=200,"7"))))))


The problem is, these formulas work fine in separate columns but I cannot seem to join them into one large formula. I have tried to join them but I keep getting 'FALSE' when D values are >80. Basically it wont recognize the second half of the formula (D>80...) when joined. Ideally I would have one column for Type rather than 2 which I currently have.

If anyone can help here I would be most grateful.

AT0

Thank you very much Special-K, you've nailed it! That works well and is a much more practical way of doing things than my previous attempt.

I appreciate your help

AT0

OK, I now have a follow-on question which I've been trying to solve based upon Special-K's recent answer. Now that Special-K has given me a formula to identify 'Type' I now need to make additional categories based upon these types.

So, If column D is type 1,2,4 or 6 I need to test values in column F against a set of criteria to display a category in column G. For example, If column D is type 1, 2, 4 or 6 and F is<=3 then G should be "H". If F is<=4 the column G should be "G". If F is <=6 then column G should be "M" and so on to category "P". I have adapted Special-K's formula to this which seems to work:

=IF(OR(D2=1,D2=2,D2=4,D2=6),LOOKUP(F2,{-1E+99,3,4,6,7.5},{"H","G","M","P"}))

The next step is to repeat this for types (column D) 3, 5 and 7, which have a different set of criteria. For example If column D is type 3, 5 or 7 and F is <=4 then column G should be "H". I have used this which seems OK:

=IF(OR(D2=3,D2=5,D2=7),LOOKUP(F2,{-1E+99,4,5,6.5,9},{"H","G","M","P"}))

My problem is once again joining the formulae together. I have tried the approach suggested by Special-k which worked for the previous problem, but it doesn't seem to work here. i.e. I've tried:

=IF(OR(D2=1,D2=2,D2=4,D2=6),LOOKUP(F2,{-1E+99,3,4,6,7.5},{"H","G","M","P"}),LOOKUP(F2,{-1E+99,4,5,6.5,9},{"H","G","M","P"}))

But this doesn't seem to work.
I have been researching the Lookup function and I can't seem to grasp what is wrong here. Again, it doesn't account for D being 3,5 or 7 this time.

If anyone can put me right here and explain what is going wrong I'd be very grateful.

Yours,

AT0