I haven't been able to come up with an appropriate formula for this
one, I hope someone can point me in the right direction.

I have a list of article numbers (the format is like 123.45.678). Next,
I have another list of article number ranges and its corresponding
product group (say from 123.45.601 to 123.45.700 is "Screws"). This
list has 3 columns: First article number in range/group, last article
number in range/group, and the name of the group.

Important: The ranges are not consistent, so in my example the next
product group up doesn't start with 123.45.701 but maybe with
124.00.000. Example:

Min_Max_Group
123.45.601 _123.45.700_Screws
124.00.000_124.99.999_Bolts
220.70.000_220.80.999_Casing

Now I need a formula that checks if an article number is = the minimum
value and <= the maximum value and then returns the group. (and, if
possible, to return "no group" if there is no range for that number,
like for 123.45.800)

So far I have been able to come up with a formula that checks if the
number is inbetween the min and max value (with sumproduct) and returns
TRUE or FALSE. But I can't figure out how to make the necessary lookup
in the Group column.

Thanks in advance
Andreas

Hi!

How did you do a = / <= comparison? You'd have to "remove" at least one of
the decimal points to coerce the TEXT strings into numbers.

Based on the limited sample.

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

A9 = lookup value

=INDEX(C2:C4,MATCH(1,(--SUBSTITUTE(A9,".","")=--SUBSTITUTE(A2:A4,".",""))*(--SUBSTITUTE(A9,".","")<=--SUBSTITUTE(B2:B4,".","")),0))

With an error trap to return "no group":

=IF(ISNA(MATCH(1,(--SUBSTITUTE(A9,".","")=--SUBSTITUTE(A2:A4,".",""))*(--SUBSTITUTE(A9,".","")<=--SUBSTITUTE(B2:B4,".","")),0)),"no
group",INDEX(C2:C4,MATCH(1,(--SUBSTITUTE(A9,".","")=--SUBSTITUTE(A2:A4,".",""))*(--SUBSTITUTE(A9,".","")<=--SUBSTITUTE(B2:B4,".","")),0)))

That's pretty nasty!

It would be easier if you could just eliminate the decimal points and work
with NUMERIC values!

Biff

wrote in message
oups.com...
I haven't been able to come up with an appropriate formula for this
one, I hope someone can point me in the right direction.

I have a list of article numbers (the format is like 123.45.678). Next,
I have another list of article number ranges and its corresponding
product group (say from 123.45.601 to 123.45.700 is "Screws"). This
list has 3 columns: First article number in range/group, last article
number in range/group, and the name of the group.

Important: The ranges are not consistent, so in my example the next
product group up doesn't start with 123.45.701 but maybe with
124.00.000. Example:

Min_Max_Group
123.45.601 _123.45.700_Screws
124.00.000_124.99.999_Bolts
220.70.000_220.80.999_Casing

Now I need a formula that checks if an article number is = the minimum
value and <= the maximum value and then returns the group. (and, if
possible, to return "no group" if there is no range for that number,
like for 123.45.800)

So far I have been able to come up with a formula that checks if the
number is inbetween the min and max value (with sumproduct) and returns
TRUE or FALSE. But I can't figure out how to make the necessary lookup
in the Group column.

Thanks in advance
Andreas

Hello Biff,

Thanks for your input.

Firstly, the dot is not a decimal mark, it's really meant as a dot (and
where I live the decimal sign is the comma). That's the format of the
article numbers I work with. It's 3 digits - dot - 2 digits - dot - 3
digits. (Sometimes with an occasional letter in between, but that
shouldn't bother us right now).

My experience with Excel (correct me if I am wrong) is that it knows
how to sort those expression. It treats them like numbers, so no need
to remove or replace the dots.

Secondly, my formula is =SUMPRODUCT(--('Product
Groups'!$A$2:$A$42<=Sheet1!A1);--('Product
Groups'!$B$2:$B$42=Sheet1!A1))

Sheet1!A1 is the lookup value, and in 'Product Groups' we find the
columns Min (A), Max (B) and Group (C). This formula does not have to
be entered as an array formula.

The solution I am looking for is, in clear text: If you find the lookup
value to be part of a range in the array (in other words if the result
of the formula above is TRUE), then go to column C in that particular
row (that column has the Product Group name) and give me the value you
find there. If the sumproduct is FALSE, give me "no group".

I appreciate your help.

Andreas

Hello Biff,

Thanks for your input.

Firstly, the dot is not a decimal mark, it's really meant as a dot (and
where I live the decimal sign is the comma). That's the format of the
article numbers I work with. It's 3 digits - dot - 2 digits - dot - 3
digits. (Sometimes with an occasional letter in between, but that
shouldn't bother us right now).

My experience with Excel (correct me if I am wrong) is that it knows
how to sort those expression. It treats them like numbers, so no need
to remove or replace the dots.

Secondly, my formula is =SUMPRODUCT(--('Product
Groups'!$A$2:$A$42<=Sheet1!A1);--('Product
Groups'!$B$2:$B$42=Sheet1!A1))

Sheet1!A1 is the lookup value, and in 'Product Groups' we find the
columns Min (A), Max (B) and Group (C). This formula does not have to
be entered as an array formula.

The solution I am looking for is, in clear text: If you find the lookup
value to be part of a range in the array (in other words if the result
of the formula above is TRUE), then go to column C in that particular
row (that column has the Product Group name) and give me the value you
find there. If the sumproduct is FALSE, give me "no group".

I appreciate your help.

Andreas

OK, I'll take your word that the "dot" is really a "comma" and is the normal
number separator in you location!

That makes things much simpler:

Still array entered:

=IF(ISNA(MATCH(1,(A9=A2:A4)*(A9<=B2:B4),0)),"no
group",INDEX(C2:C4,MATCH(1,(A9=A2:A4)*(A9<=B2:B4) ,0)))

Biff

wrote in message
oups.com...
Hello Biff,

Thanks for your input.

Firstly, the dot is not a decimal mark, it's really meant as a dot (and
where I live the decimal sign is the comma). That's the format of the
article numbers I work with. It's 3 digits - dot - 2 digits - dot - 3
digits. (Sometimes with an occasional letter in between, but that
shouldn't bother us right now).

My experience with Excel (correct me if I am wrong) is that it knows
how to sort those expression. It treats them like numbers, so no need
to remove or replace the dots.

Secondly, my formula is =SUMPRODUCT(--('Product
Groups'!$A$2:$A$42<=Sheet1!A1);--('Product
Groups'!$B$2:$B$42=Sheet1!A1))

Sheet1!A1 is the lookup value, and in 'Product Groups' we find the
columns Min (A), Max (B) and Group (C). This formula does not have to
be entered as an array formula.

The solution I am looking for is, in clear text: If you find the lookup
value to be part of a range in the array (in other words if the result
of the formula above is TRUE), then go to column C in that particular
row (that column has the Product Group name) and give me the value you
find there. If the sumproduct is FALSE, give me "no group".

I appreciate your help.

Andreas

YES!

Thanks a lot, it works.

Grettings from Indonesia
Andreas

You're welcome. Thanks for the feedback!

Biff

wrote in message
ps.com...
YES!

Thanks a lot, it works.

Grettings from Indonesia
Andreas