jski
Board Regular
- Joined
- Jan 11, 2006
- Messages
- 118
Good morning Mr. Excel team,
I'm trying to develop a formula that would permit me to lookup a value within a dollar range (Revenue Growth), a percentage range (% Growth), and return in an award amount (Payout). The table looks like this:
[TABLE="width: 412"]
<tbody>[TR]
[TD="colspan: 2"]Revenue Growth[/TD]
[TD="colspan: 2"]% Growth[/TD]
[TD] Payout[/TD]
[/TR]
[TR]
[TD] From (A3)[/TD]
[TD] To [/TD]
[TD="align: left"]
<tbody>
</tbody>[/TD]
[TD] To [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]15.00%[/TD]
[TD] 1000000%[/TD]
[TD] 3.75%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]10.00%[/TD]
[TD] 14.99%[/TD]
[TD] 2.75%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]8.00%[/TD]
[TD] 9.99%[/TD]
[TD] 2.25%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]7.00%[/TD]
[TD] 7.99%[/TD]
[TD] 1.75%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]6.00%[/TD]
[TD] 6.99%[/TD]
[TD] 1.50%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]0% [/TD]
[TD] 5.99%[/TD]
[TD] 0%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]11.00%[/TD]
[TD] 14.99%[/TD]
[TD] 3.75%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]7.00%[/TD]
[TD]10.99%[/TD]
[TD] 2.75%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]6.00%[/TD]
[TD]6.99%[/TD]
[TD] 2.25%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]5.00%[/TD]
[TD]5.99%[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]4.00%[/TD]
[TD]4.99%[/TD]
[TD]1.50%[/TD]
[/TR]
[TR]
[TD="align: left"]
<tbody>
</tbody>[/TD]
[TD]$999,999 [/TD]
[TD]0%[/TD]
[TD]3.99%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 10.00%[/TD]
[TD]11%[/TD]
[TD]3.75%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 6.00%[/TD]
[TD]9.99%[/TD]
[TD]2.75%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 5.00%[/TD]
[TD]5.99%[/TD]
[TD]2.25%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 4.00%[/TD]
[TD]4.99%[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 3.00%[/TD]
[TD]3.99%[/TD]
[TD]1.50% (E20) [/TD]
[/TR]
[TR]
[TD]I assume an array formula? Thanks.[/TD]
[TD]
[/TD]
[TD] [/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]
I'm trying to develop a formula that would permit me to lookup a value within a dollar range (Revenue Growth), a percentage range (% Growth), and return in an award amount (Payout). The table looks like this:
[TABLE="width: 412"]
<tbody>[TR]
[TD="colspan: 2"]Revenue Growth[/TD]
[TD="colspan: 2"]% Growth[/TD]
[TD] Payout[/TD]
[/TR]
[TR]
[TD] From (A3)[/TD]
[TD] To [/TD]
[TD="align: left"]
From |
<tbody>
</tbody>
[TD] To [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]15.00%[/TD]
[TD] 1000000%[/TD]
[TD] 3.75%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]10.00%[/TD]
[TD] 14.99%[/TD]
[TD] 2.75%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]8.00%[/TD]
[TD] 9.99%[/TD]
[TD] 2.25%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]7.00%[/TD]
[TD] 7.99%[/TD]
[TD] 1.75%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]6.00%[/TD]
[TD] 6.99%[/TD]
[TD] 1.50%[/TD]
[/TR]
[TR]
[TD]$500,000 [/TD]
[TD]$749,999 [/TD]
[TD]0% [/TD]
[TD] 5.99%[/TD]
[TD] 0%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]11.00%[/TD]
[TD] 14.99%[/TD]
[TD] 3.75%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]7.00%[/TD]
[TD]10.99%[/TD]
[TD] 2.75%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]6.00%[/TD]
[TD]6.99%[/TD]
[TD] 2.25%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]5.00%[/TD]
[TD]5.99%[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD]$750,000 [/TD]
[TD]$999,999 [/TD]
[TD]4.00%[/TD]
[TD]4.99%[/TD]
[TD]1.50%[/TD]
[/TR]
[TR]
[TD="align: left"]
$750,000 |
<tbody>
</tbody>
[TD]$999,999 [/TD]
[TD]0%[/TD]
[TD]3.99%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 10.00%[/TD]
[TD]11%[/TD]
[TD]3.75%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 6.00%[/TD]
[TD]9.99%[/TD]
[TD]2.75%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 5.00%[/TD]
[TD]5.99%[/TD]
[TD]2.25%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 4.00%[/TD]
[TD]4.99%[/TD]
[TD]1.75%[/TD]
[/TR]
[TR]
[TD]$1,000,000 [/TD]
[TD]100,000,000[/TD]
[TD] 3.00%[/TD]
[TD]3.99%[/TD]
[TD]1.50% (E20) [/TD]
[/TR]
[TR]
[TD]I assume an array formula? Thanks.[/TD]
[TD]
[/TD]
[TD] [/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody><colgroup><col><col span="4"></colgroup>[/TABLE]