Help in Lookup Range

[Jerome_RA]

Hi Everyone.

Maybe you can help me with this question.
I have here an example with this range of data.
Data below is the RAW.
What I want to do is to get the Overall Score from the Unique Column which is the Description and Month containing what Range there Grades are. So for Instance, I have 98.5% on Math for July. So the Score would be 4.

Description and Month are just a sample. This could be a whole year (January - December) with lots of Description entered. So the Data below is just a sample of what I want to get the formula.

UID	Description	Month	From	To	Overall Score
MathJuly <tbody> </tbody><colgroup><col></colgroup> <tbody> </tbody><colgroup><col></colgroup>	Math <tbody> </tbody><colgroup><col></colgroup>	July	99%	99999%	5
MathJuly <tbody> </tbody><colgroup><col></colgroup>	Math <tbody> </tbody><colgroup><col></colgroup>	July	97.5%	98.99%	4
MathJuly	Math <tbody> </tbody><colgroup><col></colgroup>	July	95%	97.49%	3
MathJuly	Math <tbody> </tbody><colgroup><col></colgroup>	July	90%	94.99%	2
MathJuly	Math <tbody> </tbody><colgroup><col></colgroup>	July	0%	89.99%	1
ScienceAugust <tbody> </tbody><colgroup><col></colgroup>	Science <tbody> </tbody><colgroup><col></colgroup>	August	99%	99999%	5
ScienceAugust <tbody> </tbody><colgroup><col></colgroup>	Science <tbody> </tbody><colgroup><col></colgroup>	August	97.5%	98.99%	4
ScienceAugust <tbody> </tbody><colgroup><col></colgroup>	Science <tbody> </tbody><colgroup><col></colgroup>	August	95%	97.49%	3
ScienceAugust <tbody> </tbody><colgroup><col></colgroup>	Science <tbody> </tbody><colgroup><col></colgroup>	August	90%	94.99%	2
ScienceAugust <tbody> </tbody><colgroup><col></colgroup>	Science <tbody> </tbody><colgroup><col></colgroup>	August	0%	89.99%	1

<tbody>
</tbody>

 

[Jerome_RA]

Thanks, Nishant94. Thank you so much for your help. However, it will not show the correct Overall Score. For Instance, I type in 0 for C14, then the OverallScore would be 16 instead of 1. And if I change C14 to 99%, then the Overall Score would become 6. If I change A14 to Science, with 99% on C14, then it would show 0 on the Overall Score.

Try this using the same setup as above,

=SUMPRODUCT((((C14>=(($A$2:$A$11=A14&B14)*$D$2:$D$11))+(C14<=(($A$2:$A$11=A14&B14)*$E$2:$E$11)))=2)*$F$2:$F$11)

 

[Jerome_RA]

Hi Guys, still help on this Problem. Any other Ideas on how to do this? huhuhuhhuuhhu

 

[FormR]

Hi, something like this maybe..:

Excel 2013/2016
	A	B	C	D	E	F
1	UID	Description	Month	From	To	Overall Score
2	MathJuly	Math	July	99%	99999%	5
3	MathJuly	Math	July	98%	99%	4
4	MathJuly	Math	July	95%	97%	3
5	MathJuly	Math	July	90%	95%	2
6	MathJuly	Math	July	0%	90%	1
7	ScienceAugust	Science	August	99%	99999%	5
8	ScienceAugust	Science	August	98%	99%	4
9	ScienceAugust	Science	August	95%	97%	3
10	ScienceAugust	Science	August	90%	95%	2
11	ScienceAugust	Science	August	0%	90%	1
12
13
14	Math	July	98.50%	4
Sheet1

Cell Formulas
Range	Formula
D14	{=INDEX($F$2:$F$11,MATCH(C14,IF(A2:A11=A14&B14,E2:E11),-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jerome_RA]

OMG FormR, THANK YOU!!!!! You're a genius!!!! This is what I need!!!! OMG!!!!! I could hug you right now.

Hi, something like this maybe..:

Excel 2013/2016

	A	B	C	D	E	F
1	UID	Description	Month	From	To	Overall Score
2	MathJuly	Math	July	99%	99999%	5
3	MathJuly	Math	July	98%	99%	4
4	MathJuly	Math	July	95%	97%	3
5	MathJuly	Math	July	90%	95%	2
6	MathJuly	Math	July	0%	90%	1
7	ScienceAugust	Science	August	99%	99999%	5
8	ScienceAugust	Science	August	98%	99%	4
9	ScienceAugust	Science	August	95%	97%	3
10	ScienceAugust	Science	August	90%	95%	2
11	ScienceAugust	Science	August	0%	90%	1
12
13
14	Math	July	98.50%	4

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D14 {=INDEX($F$2:$F$11,MATCH(C14,IF(A2:A11=A14&B14,E2:E11),-1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>