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

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

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

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.

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.

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.