Lookup table

[aamilkhan]

Hi everyone,

I am having hard time thinking about this. Any help will be greatly appreciated.

Here is what i am trying to acheive

In the attached sample model I want to incorporate federal rebate.
You only get rebate if the average fuel economy for the vehicle is less than 6.5 for cars ( C ) 8.3 for vans ( V ) and 13.0 for Flexible Fuel Vechicles (FFV)

The maximum rebate that you can get is $2000 and minimum $1000. You cannot get any higher or lesser than this.
So in my model if I select C in cell G6 it should look at cell E55 which is the average fuel efficiency of that vehicle and do the calculations based on the table for cars.

Looking forward to hearing from you guys with solutions.

Thanks in advance!

[WinteE]

Hi,

The following function will do the trick for you.

=SUMPRODUCT((I38:I41>E55)*(J38:J41<E55)*(K38:K41))

You have to change "or less" in cell J41 to zero to make it work right.

Succes,

Erik

[aamilkhan]

Thanks for your promt response.

Is there anyway i can use the index match function for the lookup table. I have used it in other cases where there was one column for rates and the other for amounts. Where as now i have two column for rates and one column for amounts.

Any suggestions anyone?

Thanks,

[Dean England]

aamilkhan,

You might want to take a look at DGET. It might work for you. Take a look at the attached zip file.

See ya,

Dean