INDEX using range

[BJ]

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.

 

[T. Valko]

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(B24,MATCH(A7,A2:A4),B7)

Format as Percentage

Result = 8%

 

[Harlan Grove]

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),entered_years+1)