Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a tricky problem of placing a number within an array...
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a tricky problem of placing a number within an array...
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 wrote in message oups.com... 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a tricky problem of placing a number within an array...
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)),IN DIRECT(item),0)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) | |||
Problem with array and msgbox | Excel Worksheet Functions | |||
Problem with array and msgbox | Excel Worksheet Functions | |||
Problem with array and msgbox | Excel Worksheet Functions |