B
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.
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.