software

I have products, DRESS, SHIRT, BLOUSE. Within each product type I have
a selling price structure of OKAY, GOOD, BETTER, BEST. From a list of
products I need to take the price of the garment and the type and find
the price structure for it.

OKAY GOOD BETTER BEST
DRESS lt;19.99 20-29.99 30-48.99 gt;49
SHIRT lt; 9.99 10-14.99 15-27.99 gt;28
BLOUSE lt;12.99 13-17.99 18-29.99 gt;30STRIPE BLUE SHIRT 25 quot;BETTERquot;
LONG DRESS PURPLE DRESS 60 quot;BESTquot;
DOT BLOUSE GREEN BLOUSE 15 quot;GOODquot;

So the example above reads it's a shirt and takes the price and looks
in the other array and says that it falls within the BETTER category.
I can't find a function to match within an array and can't think of a
way around it. Any ideas?Hi!

Here's one way but you'd need to change some things aound.

In your lookup table you'd have to use the lower boundary values. Example:

gt; DRESS lt;19.99 20-29.99 30-48.99 gt;49

Would need to be like this:

DRESS 0 20 30 49

Assuming this table:

gt; OKAY GOOD BETTER BEST
gt; DRESS lt;19.99 20-29.99 30-48.99 gt;49
gt; SHIRT lt; 9.99 10-14.99 15-27.99 gt;28
gt; BLOUSE lt;12.99 13-17.99 18-29.99 gt;30

Is in the range A1:E4

A10 = Shirt
B10 = 25

=INDEX(B1:E1,MATCH(B10,OFFSET(A1,MATCH(A10,A1:A4,0 )-1,1,,4)))

I can put together a sample file if it'd be easier to understand.

Biff

gt; wrote in message oups.com...
gt;I have products, DRESS, SHIRT, BLOUSE. Within each product type I have
gt; a selling price structure of OKAY, GOOD, BETTER, BEST. From a list of
gt; products I need to take the price of the garment and the type and find
gt; the price structure for it.
gt;
gt; OKAY GOOD BETTER BEST
gt; DRESS lt;19.99 20-29.99 30-48.99 gt;49
gt; SHIRT lt; 9.99 10-14.99 15-27.99 gt;28
gt; BLOUSE lt;12.99 13-17.99 18-29.99 gt;30
gt;
gt;
gt; STRIPE BLUE SHIRT 25 quot;BETTERquot;
gt; LONG DRESS PURPLE DRESS 60 quot;BESTquot;
gt; DOT BLOUSE GREEN BLOUSE 15 quot;GOODquot;
gt;
gt; So the example above reads it's a shirt and takes the price and looks
gt; in the other array and says that it falls within the BETTER category.
gt; I can't find a function to match within an array and can't think of a
gt; way around it. Any ideas?
gt;
Assume a reduced version of your data looks like this:
gradeOK GDBRBS
DRS0203049
SHR0101528
BLS0131830

itemvaluebin
SHR25BR
DRS60BS
BLS15GD

The numerical values in the first array have been reduced
to single numbers by Find/Replace
Find -??.99 Replace with (nothing)
Find lt;* Replace with zero
Find gt; Replace with (nothing)
Select the first array ( 20 cells) and
Insert gt; Name gt; Create gt; Left Column
Select the second array (12 cells) and
Insert gt; Name gt; Create gt; Top Row
In bin, enter this formula
=INDEX(grade,MATCH(LOOKUP(value,INDIRECT(item)),IN DIRECT(item),0))