Need help with a tricky problem of placing a number within an array...

[[email protected]]

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 <19.99 20-29.99 30-48.99 >49
SHIRT < 9.99 10-14.99 15-27.99 >28
BLOUSE <12.99 13-17.99 18-29.99 >30


STRIPE BLUE SHIRT 25 "BETTER"
LONG DRESS PURPLE DRESS 60 "BEST"
DOT BLOUSE GREEN BLOUSE 15 "GOOD"

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?

[Biff]

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:

> DRESS <19.99 20-29.99 30-48.99 >49

Would need to be like this:

DRESS 0 20 30 49

Assuming this table:

> OKAY GOOD BETTER BEST
> DRESS <19.99 20-29.99 30-48.99 >49
> SHIRT < 9.99 10-14.99 15-27.99 >28
> BLOUSE <12.99 13-17.99 18-29.99 >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

<[email protected]> wrote in message
news:[email protected]...
>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 <19.99 20-29.99 30-48.99 >49
> SHIRT < 9.99 10-14.99 15-27.99 >28
> BLOUSE <12.99 13-17.99 18-29.99 >30
>
>
> STRIPE BLUE SHIRT 25 "BETTER"
> LONG DRESS PURPLE DRESS 60 "BEST"
> DOT BLOUSE GREEN BLOUSE 15 "GOOD"
>
> 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?
>

[Herbert Seidenberg]

Assume a reduced version of your data looks like this:
grade OK GD BR BS
DRS 0 20 30 49
SHR 0 10 15 28
BLS 0 13 18 30

item value bin
SHR 25 BR
DRS 60 BS
BLS 15 GD

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