Exceeding the limit of Nest IF's

[Martin Koenig]

I have a worksheet where I have used Data Validation taken from
another worksheet for entry in 2 Cells(CD19 & CQ19) based on the
values in these 2 cells I would like the result to be dispayed in a
third Cell(DJ15) I tried the example on Chip Pearson's web page, but
that does not work either, since I still will exceed 7 IF's.

Is there another way to do this.

Named Range P1
=IF(AND(CD19>=1,CD19<=8,CQ19>=1,CQ19<=8),"P-N°1 - P-N°1","Not
Qualified")

Named Range P5
=IF(OR(CQ19="10A",CQ19="10B",CQ19="10C",CQ19="10C",CQ19="10H",CQ19="10I",CQ19="10J"_
,CQ19="10K")*OR(CD19="10A",CD19="10B",CD19="10C",CD19="10C",CD19="10H",CD19="10I",_
CD19="10J",CD19="10K"),"P-N° 10 to 10",IF(OR(CD19="11A",_
CD19="11B")*OR(CQ19="11A",CQ19="11B"),"P-N° 11 to 11",IF(OR(CD19="5A",
CD19="5B",_CD19="5C")*OR(CQ19="5A",CQ19="5B",CQ19="5C"),"P-N° 5 _
to 5","Not Qualified"")))

Named Range P10A
=IF(OR(CD19="10A",CD19="10B",CD19="10C",CD19="10C",CD19="10H",CD19="10I",_
CD19="10J",CD19="10K")*AND(CQ19>=1,CQ19<=8),"P-N° 10A to 1",_
IF(OR(CD19="11A",CD19="11B")*AND(CQ19>=1,CQ19<=8),"P-N° 11A to 1",_
IF(OR(CD19="5A",CD19="5B",CD19="5C")*AND(CQ19>=1,CQ19<=8),"P-N° 5A to
1",_
IF(OR(CQ19="10A",CQ19="10B",CQ19="10C",CQ19="10C",CQ19="10H",CQ19="10I",_
CQ19="10J",CQ19="10K")*AND(CD19>=1,CD19<=8),"P-N° 1 to 10A",_
IF(OR(CQ19="11A",CQ19="11B")*AND(CD19>=1,CD19<=8),"P-N° 1 to 11A",_
IF(OR(CQ19="5A",CQ19="5B",CQ19="5C")*AND(CD19>=1,CD19<=8),"_
P-N°1 to 5A","Not Qualified""))))))

Named Range P34
=IF(AND(CD19>=1,CD19<=8,CQ19>=1,CQ19=34),"P-N°1 to P-N° 34",_
IF(AND(CD19=34,CQ19>=1,CQ19<=8),"P-N° 34 to P-N° 1",IF(AND(CD19=34,_
CQ19=34),"P-N° 34 to P-N° 34","Not Qualified")))

Named Range P41
=IF(AND(CD19>=1,CD19<=8,CQ19>=41,CQ19<=47),"P-N° 1 ot P-N° 41-47",_
IF(AND(CQ19>=41,CQ19<=47,CD19>=41,CD19<=47),"P-N° 41 to P-N° 47",_
IF(AND(CQ19>=1,CD19>=41,CD19<=47),"P-N° 41-47 to P-N°1","Not_
Qualified"")))

Named Range P51

Named Range P61

etc...

Any help would be appricated.
Kind Regards
Martin

 

[Harlan Grove]

...
...

Named Range P5
=IF(OR(CQ19="10A",CQ19="10B",CQ19="10C",CQ19="10C",CQ19="10H",CQ19="10I",
CQ19="10J",CQ19="10K")*OR(CD19="10A",CD19="10B",CD19="10C",CD19="10C",
CD19="10H",CD19="10I",CD19="10J",CD19="10K"),"P-N° 10 to 10",
IF(OR(CD19="11A",CD19="11B")*OR(CQ19="11A",CQ19="11B"),"P-N° 11 to 11",
IF(OR(CD19="5A",CD19="5B",CD19="5C")*OR(CQ19="5A",CQ19="5B",CQ19="5C"),
"P-N° 5 to 5","Not Qualified"")))

Note that you're comparing CQ19 and CD19 to "10C" twice.

Any time you're comparing the same cell to multiple values it should be a clear
sign you should be using some form of lookup. Enter the following table into a
2-column range.

10A 1
10B 1
10C 1
10H 1
10I 1
10J 1
10K 1
11A 2
11B 2
5A 3
5B 3
5C 3

Define a name like PNTable referring to this range. Then change your formula to

=IF(ISNUMBER(1/(LOOKUP(CD19,PNTable)=LOOKUP(CQ19,PNTable)),
CHOOSE(LOOKUP(CD19,PNTable),"P-N° 10 to 10","P-N° 11 to 11","P-N° 5 to 5"),
"Not Qualified"")

and similarly for all your other formulas.

 

[Martin Koenig]

...
..

Note that you're comparing CQ19 and CD19 to "10C" twice.

Any time you're comparing the same cell to multiple values it should be a clear
sign you should be using some form of lookup. Enter the following table into a
2-column range.

10A 1
10B 1
10C 1
10H 1
10I 1
10J 1
10K 1
11A 2
11B 2
5A 3
5B 3
5C 3

Define a name like PNTable referring to this range. Then change your formula to

=IF(ISNUMBER(1/(LOOKUP(CD19,PNTable)=LOOKUP(CQ19,PNTable)),
CHOOSE(LOOKUP(CD19,PNTable),"P-N° 10 to 10","P-N° 11 to 11","P-N° 5 to 5"),
"Not Qualified"")

and similarly for all your other formulas.

Just wanted to say thanks, your solution works perfectly for what I
was trying to do. How easy something can be if you know what you are
doing, again thanks for your assistance.
Martin