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.

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.

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)