The error of my ways.
 

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))))))

The error of my ways.
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

The error of my ways.
 

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 dont have to bother anyone with a simple
IF/AND problem. Once again thank you for your help.

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

The error of my ways.
 

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cerberus" wrote:
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 dont have to bother anyone with a simple
IF/AND problem. Once again thank you for your help.

The error of my ways.
 

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

"Max" wrote:

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cerberus" wrote:
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 dont have to bother anyone with a simple
IF/AND problem. Once again thank you for your help.

The error of my ways.
 

Welcome, glad to help.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Cerberus" wrote in message
...
Your the man! Thank you for your help and your way was a lot easier also.