# Need value as per given criteria

Solved/Closed
Sanam Khan - Jun 20, 2015 at 06:56 PM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Jun 23, 2015 at 09:56 PM
Hello,
There is a range given on one column and on the other column there are points. Ex:
200 - 180. 10 points
179 - 160. 9.5 points
159 - 130. 8 points

How I put a formula in excel when I write 168 it should reflect 9.5 points in front of that. And if I change the value to 141 it should reflect 8 points. I have a long range starting from 0.

## 4 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jun 21, 2015 at 12:25 PM
Sanam Khan, good morning.

Try to use it, like this one:

Is this what you want.
I hope it helps.
Hello Mazzaropi, Thanks for your help but its not working.

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jun 21, 2015 at 01:38 PM

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Jun 22, 2015 at 01:31 PM
Sanam Khan, Good afternoon.

Your table of values has a very unusual way to write.

Try to use:
M4 --> =SUMPRODUCT((\$E\$5:\$E\$20>=L4)*(\$F\$5:\$F\$20<=L4)*(\$G\$5:\$G\$20))
Copy it down.

Is this what you want?
I hope it helps.
Hello Mazzaropi,

Thanks a lot for your help and support. You are Genius. You are Excel Master. Thank you so much.. You made my life. The formula is working perfectly.