Help in Lookup Range

J

Jerome_RA

Board Regular
Joined
Dec 26, 2016
Messages
53
Office Version
  1. 2013
Platform
  1. Windows
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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]UID[/TD]
[TD]Description[/TD]
[TD]Month[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Overall Score[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]MathJuly[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Math[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]July[/TD]
[TD]99%[/TD]
[TD]99999%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]MathJuly[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Math[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]July[/TD]
[TD]97.5%[/TD]
[TD]98.99%[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]MathJuly[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Math[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]July[/TD]
[TD]95%[/TD]
[TD]97.49%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]MathJuly[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Math[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]July[/TD]
[TD]90%[/TD]
[TD]94.99%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]MathJuly[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Math[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]July[/TD]
[TD]0%[/TD]
[TD]89.99%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]ScienceAugust[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Science[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]August[/TD]
[TD]99%[/TD]
[TD]99999%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]ScienceAugust[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Science[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]August[/TD]
[TD]97.5%[/TD]
[TD]98.99%[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]ScienceAugust[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Science[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]August[/TD]
[TD]95%[/TD]
[TD]97.49%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]ScienceAugust[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Science[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]August[/TD]
[TD]90%[/TD]
[TD]94.99%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD]ScienceAugust[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]Science[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]August[/TD]
[TD]0%[/TD]
[TD]89.99%[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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.

Nishant94 said:
Try this using the same setup as above,

Code: 
=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)
Click to expand...
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi Guys, still help on this Problem. Any other Ideas on how to do this? huhuhuhhuuhhu
 
Upvote 0
Hi, something like this maybe..:


Excel 2013/2016
ABCDEF
1UIDDescriptionMonthFromToOverall Score
2MathJulyMathJuly99%99999%5
3MathJulyMathJuly98%99%4
4MathJulyMathJuly95%97%3
5MathJulyMathJuly90%95%2
6MathJulyMathJuly0%90%1
7ScienceAugustScienceAugust99%99999%5
8ScienceAugustScienceAugust98%99%4
9ScienceAugustScienceAugust95%97%3
10ScienceAugustScienceAugust90%95%2
11ScienceAugustScienceAugust0%90%1
12
13
14MathJuly98.50%4
Sheet1
Cell Formulas
RangeFormula
D14{=INDEX($F$2:$F$11,MATCH(C14,IF(A2:A11=A14&B14,E2:E11),-1))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
OMG FormR, THANK YOU!!!!! You're a genius!!!! This is what I need!!!! OMG!!!!! I could hug you right now.


FormR said:
Hi, something like this maybe..:

Excel 2013/2016
ABCDEF
UIDDescriptionMonthFromToOverall Score
MathJulyMathJuly
MathJulyMathJuly
MathJulyMathJuly
MathJulyMathJuly
MathJulyMathJuly
ScienceAugustScienceAugust
ScienceAugustScienceAugust
ScienceAugustScienceAugust
ScienceAugustScienceAugust
ScienceAugustScienceAugust
MathJuly

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]99%[/TD]
[TD="align: right"]99999%[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]98%[/TD]
[TD="align: right"]99%[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]95%[/TD]
[TD="align: right"]97%[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]90%[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0%[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]99%[/TD]
[TD="align: right"]99999%[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]98%[/TD]
[TD="align: right"]99%[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]95%[/TD]
[TD="align: right"]97%[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]90%[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]0%[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]98.50%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D14[/TH]
[TD="align: left"]{=INDEX($F$2:$F$11,MATCH(C14,IF(A2:A11=A14&B14,E2:E11),-1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Excel XLookup HELP
Replies
5
Views
604
Fluff
Fluff
B
  • Question Question
weighted sum
Replies
7
Views
931
Eric W
Eric W
R
  • Locked
  • Question Question
Assistance requested writing macro to prepare data for pivot table. THANK YOU!
Replies
2
Views
509
Fluff
Fluff
R
  • Question Question
Macro to convert data to a more standard form?
2 3 4
Replies
30
Views
3K
Fluff
Fluff
N
  • Question Question
VBA Help required
Replies
0
Views
608
nikhil0311
N

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top