Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX using range
want to lookup an amount based on two variables, units and years
For example: No. of Units 1 yr 2 yr 3yr 0,001 - 1,000 0% 3% 4% 1,001 - 2,000 3% 5% 8% 2,001 - 3,000 4% 7% 16% The user will input the no. of units so I need to lookup that value within a specific range. I had been using VLOOKUP and HLOOKUP to locate the year and unit value in order to use the INDEX function, but now that I've got a range instead of a single no. I'm a bit stumped. The ranges are quite extensive so I can't use the IF statement to determine which range the value falls into. I can do workarounds using the IF statement to populate another column to delineate the appropriate range, etc. but was hoping to get all of this in one formula. Any help is appreciated. thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX using range
One way...
Use a single value for the units. Use the *lower boundary* for each interval. No. of Units........1 yr.....2 yr......3yr 1.........................0%......3%......4% 1,001..................3%......5%......8% 2,001..................4%......7%......16% Lookup 1124 units for 3yr A7 = 1124 B7 = 3 =INDEX(B2:D4,MATCH(A7,A2:A4),B7) Format as Percentage Result = 8% -- Biff Microsoft Excel MVP "BJ" wrote in message ... want to lookup an amount based on two variables, units and years For example: No. of Units 1 yr 2 yr 3yr 0,001 - 1,000 0% 3% 4% 1,001 - 2,000 3% 5% 8% 2,001 - 3,000 4% 7% 16% The user will input the no. of units so I need to lookup that value within a specific range. I had been using VLOOKUP and HLOOKUP to locate the year and unit value in order to use the INDEX function, but now that I've got a range instead of a single no. I'm a bit stumped. The ranges are quite extensive so I can't use the IF statement to determine which range the value falls into. I can do workarounds using the IF statement to populate another column to delineate the appropriate range, etc. but was hoping to get all of this in one formula. Any help is appreciated. thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX using range
BJ wrote...
want to lookup an amount based on two variables, units and years For example: No. of Units 1 yr 2 yr 3yr 0,001 - 1,000 0% 3% 4% 1,001 - 2,000 3% 5% 8% 2,001 - 3,000 4% 7% 16% .... So the problem is that these ranges are a single column of text? If the number of units ranges were in C5:C100, easiest to define a name like NumUnitsRng referring to the formula =--LEFT($C$5:$C$100,FIND("-",$C$5:$C$100)-1) then use INDEX(..,MATCH(..),..), e.g., =INDEX($C$5:$F $100,MATCH(entered_number_of_units,NumUnitsRng),en tered_years+1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index to define a range | Excel Worksheet Functions | |||
Index by Range | Excel Worksheet Functions | |||
Index-Match from a range | Excel Worksheet Functions | |||
Use Index to get range | Excel Worksheet Functions | |||
index to a range of cells | Excel Worksheet Functions |