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