I need help with an excel problem I've just spent 3 hours doing:

1.Calculate the award levels for each manager in column D, as follows:

If their grand total is below 20,000, they get no Award.

If their grand total falls between 20,001 and 40,000, their award is Copper.

If their grand total falls between 40,001 and 60,000, their award is Bronze.

If their grand total falls between 60,001 and 100,000, their award is Silver.

If their grand total is greater than 100,000, their award is Gold.

Do not do these calculations manually, individually, or with nested If statements. Instead, use a formula that makes reference to the Award table in columns I K.

Basically I need to reference the award table and create a formula that satifies the above asks, So far I have:

=IF(B9$I$4,B9$I$5,B9$I$6,B9$I$7,$J$7)))))

But that formuals only working for the "No award"

I need to able to drag from award and to the rest of the cells and not get "#VALUE" and get either "No award", "copper", "bronze" ETC.

Thanks

Bonus Managers Division A Marlboro Scully Laureat Hamilton 5% Grand Total Manager Bonus Award 18.289 No Bonus No Award 34,416 S1,720.80 #VALUE! 9.862 No Bonus No Award 43.773 $2.188.65 #VALUE Award Table Grand Total Amount Award Add Bonus No Award 0% 20,000 Copper 5% 10% 40,000 Bronze 60,000 15% Award Am Silver 30% 100,000 Gold Series Projected 2006" Point "Business & Investing" Value: 300,000 (29%)