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]
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
=LOOKUP(N4,{0,90,95,97.5},{1,2,3,4,5})
n4 is the column with the percentage
 
Upvote 0
Thanks, Simon4s. However, I am looking for a formula that is not with a fixed Numbers since the numbers of the RAW is a configuration that constantly change every month.

Simon4s said:
=LOOKUP(N4,{0,90,95,97.5},{1,2,3,4,5})
n4 is the column with the percentage
Click to expand...
 
Upvote 0
Jerome_RA said:
Thanks, Simon4s. However, I am looking for a formula that is not with a fixed Numbers since the numbers of the RAW is a configuration that constantly change every month.
Click to expand...

I dont understand what you mean.

What do you mean raw? What numbers are changing?
 
Upvote 0
Oh! Let me rephrase that. The Table above is the Configuration. So for instance, If I have 98.5% on Math for July, the result is 4. Or if I have a Percentage of 97% for Science at August, then the result is 4. Each Month, the Table Configuration may Change so that is the reason I want the Formula to be Dynamic. :)


Simon4s said:
I dont understand what you mean.

What do you mean raw? What numbers are changing?
Click to expand...
 
Upvote 0
Jerome_RA said:
Oh! Let me rephrase that. The Table above is the Configuration. So for instance, If I have 98.5% on Math for July, the result is 4. Or if I have a Percentage of 97% for Science at August, then the result is 4. Each Month, the Table Configuration may Change so that is the reason I want the Formula to be Dynamic. :)
Click to expand...

Sorry can't help you. From what you describe, it sounds like my formula will still work??
 
Upvote 0
Your formula will work if the range is not fixed but the Configuration always changes. That is why I am still looking for other solution. Thanks, Simon4s

Simon4s said:
Sorry can't help you. From what you describe, it sounds like my formula will still work??
Click to expand...
 
Upvote 0
Is this what you are looking for:


Unknown
ABCDEF
1UIDDescriptionMonthFromToOverall Score
2MathJulyMathJuly99%99999%5
3MathJulyMathJuly97.50%98.99%4
4MathJulyMathJuly95%97.49%3
5MathJulyMathJuly90%94.99%2
6MathJulyMathJuly0%89.99%1
7ScienceAugustScienceAugust99%99999%5
8ScienceAugustScienceAugust97.50%98.99%4
9ScienceAugustScienceAugust95%97.49%3
10ScienceAugustScienceAugust90%94.99%2
11ScienceAugustScienceAugust0%89.99%1
12
13DescriptionMonthPercentageOverall Score
14MathJuly98.50%4
15
Sheet12
Cell Formulas
RangeFormula
D14{=LOOKUP(C14,CHOOSE({1,2},SMALL(IF(A14&B14=$A$2:$A$11,$D$2:$D$11), ROW(INDIRECT("1:"&COUNTIF($A$2:$A$11,A14&B14)))), ROW(INDIRECT("1:"&COUNTIF($A$2:$A$11,A14&B14)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Thanks for this, Nishant94. Tried the formula. It worked. But, if I change the Numbers from the Overall Score, the answer would then be incorrect. Any idea how to get this? :) Thank you so much for your help.


Nishant94 said:
Is this what you are looking for:

Unknown
ABCDEF
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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]UID[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Month[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]From[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]To[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Overall Score[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]89.99%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , 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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Month[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Percentage[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Overall Score[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]98.50%[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/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]

</tbody>
Sheet12​

[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"]{=LOOKUP(C14,CHOOSE({1,2},SMALL(IF(A14&B14=$A$2:$A$11,$D$2:$D$11),
ROW(INDIRECT("1:"&COUNTIF($A$2:$A$11,A14&B14)))),
ROW(INDIRECT("1:"&COUNTIF($A$2:$A$11,A14&B14)))))}[/TD]
[/TR]
</tbody>[/TABLE]
Enter the formula with "Ctrl+Shift+Enter". Only Pressing "Enter" will result in formula giving error or incorrect result. 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
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)
 
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,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

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