I have 2 different products with 7 options each and I was trying to put them
into 6 categories. The products are "TOP HINGE" and "OFFSET TOP HINGE" and
they each have an option of "O EXT", "12 EXT", "18 EXT", "24 EXT", "30 EXT",
"36 EXT", "48 EXT". Now I am trying to give "TOP HINGE-0 EXT" a value of 1,
"TOP HINGE-12 EXT" a value of 2, and "TOP HINGE-18 EXT" on up a value of 3
and "OFFSET TOP HINGE" option follows that same pattern.

I used =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))))))

But it seems to be too many functions or I missed something. Is there any
help for this? I definitely appreciate any help.

Thank you in advance.

sorry to waste your time I found my error :)

"Cerberus" wrote:

I have 2 different products with 7 options each and I was trying to put them
into 6 categories. The products are "TOP HINGE" and "OFFSET TOP HINGE" and
they each have an option of "O EXT", "12 EXT", "18 EXT", "24 EXT", "30 EXT",
"36 EXT", "48 EXT". Now I am trying to give "TOP HINGE-0 EXT" a value of 1,
"TOP HINGE-12 EXT" a value of 2, and "TOP HINGE-18 EXT" on up a value of 3
and "OFFSET TOP HINGE" option follows that same pattern.

I used =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))))))

But it seems to be too many functions or I missed something. Is there any
help for this? I definitely appreciate any help.

Thank you in advance.

"Cerberus" wrote:
sorry to waste your time I found my error :)

Error aside, see my response for one far easier way to handle it via
index/match
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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
---
"Cerberus" wrote:
I have 2 different products with 7 options each and I was trying to put them
into 6 categories. The products are "TOP HINGE" and "OFFSET TOP HINGE" and
they each have an option of "O EXT", "12 EXT", "18 EXT", "24 EXT", "30 EXT",
"36 EXT", "48 EXT". Now I am trying to give "TOP HINGE-0 EXT" a value of 1,
"TOP HINGE-12 EXT" a value of 2, and "TOP HINGE-18 EXT" on up a value of 3
and "OFFSET TOP HINGE" option follows that same pattern.

I used =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))))))

But it seems to be too many functions or I missed something. Is there any
help for this? I definitely appreciate any help.

Thank you in advance.