The error of my ways.

[Cerberus]

in the function below, I am having an issue with 3 and 6 showing up in the
cell. 0,1,2,4,5 all come up fine but I seem to have an issue after the
IF/AND. Can anyone see what I did wrong. Thanks for any help

=IF(M52="TOP HINGE-0 EXT",1,IF(M52="TOP HINGE-12 EXT",2,IF(AND(M52="TOP
HINGE-18 EXT",M52="TOP HINGE-24 EXT",M52="TOP HINGE-30 EXT",M52="TOP HINGE-36
EXT",M52="TOP HINGE-48 EXT"),3,IF(M52="OFFSET TOP HINGE-0
EXT",4,IF(M52="OFFSET TOP HINGE-12 EXT",5,IF(AND(M52="OFFSET TOP HINGE-18
EXT",M52="OFFSET TOP HINGE-24 EXT",M52="OFFSET TOP HINGE-30 EXT",M52="OFFSET
TOP HINGE-36 EXT",M52="OFFSET TOP HINGE-48 EXT"),6,0))))))

 

[Max]

I posted the response below just a while ago in your earlier thread
Give it a try, think you might find it to your taste
---------
One far easier way is to use index/match for this

Suppose you list the lookup/reference table
in Sheet2's cols A & B (from row1 down) like this:

TOP HINGE-0 EXT 1
TOP HINGE-12 EXT 2
TOP HINGE-18 EXT 3
TOP HINGE-24 EXT 3
TOP HINGE-30 EXT 3
etc

Then in another Sheet1 (say),
with M52 containing the lookup value, eg: TOP HINGE-24 EXT
you could use in say, N52:
=IF(ISNA(MATCH(M52,Sheet2!A:A,0)),0,INDEX(Sheet2!B:B,MATCH(M52,Sheet2!A:A,0)))
to return the required result of: 3. N52 can be copied down to return
correspondingly for other lookup values in M53, M54

---

 

[Cerberus]

Thank you Max, I will definitely try it your way since it seems easier.
Since I am new at this excel thing I would also like to know what I did wrong
in the formula I came up with so I don’t have to bother anyone with a simple
IF/AND problem. Once again thank you for your help.

 

[Max]

Think your use of AND in for eg this part:
.... IF(AND(M52="TOP HINGE-18 EXT",M52="TOP HINGE-24 EXT",
M52="TOP HINGE-30 EXT",M52="TOP HINGE-36 EXT",
M52="TOP HINGE-48 EXT"),3,...

should instead be an OR, viz it should be:
.... IF(OR(M52="TOP HINGE-18 EXT",M52="TOP HINGE-24 EXT",
M52="TOP HINGE-30 EXT",M52="TOP HINGE-36 EXT",
M52="TOP HINGE-48 EXT"),3,...

 

[Cerberus]

Your the man! Thank you for your help and your way was alot easier also.

 

[Max]

Welcome, glad to help.