Array and IF function issue

[Eftychia]

Hi,

In the attached file you can see in the column A a scale from 90-150 which is the achievement scale for the contact rate in column B. If you achieve a contact rate 94,4 your achievement is 148. But since the contact rate doesn t have to be the exact number of the column B, I created the columns C & D in order to cover all the possible options (so in order to achieve 148 you have to bring contact rate from [94,25 - 94,55).

In column H you can see the actual contact rate and in column I , I was supposed to put the achievement..

I thought that maybe I should use the IF function with an array...but I don t know how...(never had to use an array before).

Can anyone please help me ?

I don t know if it matters but the attachement is a 2003 xls file (I made it at work where I use excel 2003).

[bebo021999]

Try this:
=IF(H2<70.5,90,SUMPRODUCT(($C$2:$C$61<=H2)*($D$2:$D$61>=H2)*$A$2:$A$61))

[Eftychia]

It works!!!! Thank you very much!

So sumproduct was the answer... I am going to use this a looooot!

Thank u!!