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

[matthew.webb]

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

 

[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))