Having trouble with a long Nested IF Statement - HELP!

M

Mettaya1

New Member
Joined
Oct 8, 2013
Messages
20
4
6

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022; width:83pt" span="2" width="110"> </colgroup><tbody>
[TD="class: xl69, width: 110"] F

Original Score
[/TD]
[TD="class: xl70, width: 110"] G

Rating
[/TD]

[TD="class: xl68"]26[/TD]

[TD="class: xl65"]18
[/TD]

</tbody>

I have created a nested IF statement to average out the original scores to the table below
[TABLE="width: 358"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Original Score
[/TD]
[TD]Rating[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1-5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6-10[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]11-15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]16-20[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]21-25[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]26-30[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]31-35[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]36-40[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]41-50[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]51+[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

=IF(F3=0,10,IF(F3="", "",IF(AND(F3>=1,F3<=5),9,IF(AND(F3>=6,F3<=10),8,IF(AND(F3>=11,F3<=15),7,IF(AND(F3>=16,F3<=20),6,IF(AND(F3>=21,F3<=25),5,IF(AND(F3>=26,F3<=30),4,IF(AND(F3>=31,F3<=35),3,IF(AND(F3>=36,F3<=40),2,IF(AND(F3>=41,F3<=50),1,IF(AND(F3>50,F3<=1500),0))))))))))))

It works fine except that if there is a blank cell in F I get a rating of 10. I just want it to give a blank if the original score was blank instead of 10.

Any help would be appreciated.:confused:

Regards Mettraya1
 
Sort by date Sort by votes

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
That's not exactly an error in your condition statement. It's just the explanation for why blank cells are turning red. You could still format ratings of 0 with red and leave blanks unformatted, by either following my instruction above, or modifying the 0 is red formula to include a <>"" statement. For example, =AND(G2=0,G2<>"").
 
Upvote 0
markmzz said:
what you got?

Could you post the formula that you used?

Markmzz
Click to expand...

=if(f2="","",if(f2=0,10,if(f2<=5,9,if(f2<=10,8,if(f2<=15,7,if(f2<=20,6,if(f2<=25,5,if(f2<=30,4,if(f2<=35,3,if(f2<=40,2,if(f2<=50,1,if(f2<=1500,0,""))))))))))))

I also used in the rating side in Column B:
=IF(A3<>"",IF(ISNUMBER(A1),A2&" to "&A3,A2),A2&"+")
 
Upvote 0
Mettaya1 said:
This didn't work on my sheet Markmzz
Click to expand...

markmzz said:
What you got?

Could you post the formula that you used?

Markmzz
Click to expand...

Mettaya1 said:
=if(f2="","",if(f2=0,10,if(f2<=5,9,if(f2<=10,8,if(f2<=15,7,if(f2<=20,6,if(f2<=25,5,if(f2<=30,4,if(f2<=35,3,if(f2<=40,2,if(f2<=50,1,if(f2<=1500,0,""))))))))))))

I also used in the rating side in Column B:
=IF(A3<>"",IF(ISNUMBER(A1),A2&" to "&A3,A2),A2&"+")
Click to expand...

Sorry, but I'm talking about my formula of the post #7. Here it worked with your layout of post #1 and #5. Look at this:

Post #1 layout

[TABLE="width: 188"]
<colgroup><col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;" span="3"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <tbody>[TR]
[TD="class: xl63, width: 37, bgcolor: transparent"]Score[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"]Rating[/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 72, bgcolor: transparent"]Original Score[/TD]
[TD="class: xl63, width: 52, bgcolor: transparent"]Rating[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]0[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1-5[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]6-10[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]11-15[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]16-20[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]21-25[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]26-30[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]31-35[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]36-40[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]41-50[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]51+[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]******[/TD]
[TD="class: xl63, bgcolor: transparent"]******[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]*************[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[/TR]
</tbody>[/TABLE]

Formula for post #1 layout

Code: 
G2-> =IF(F2="","",LOOKUP(F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))


Post #5 layout

[TABLE="width: 248"]
<colgroup><col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <tbody>[TR]
[TD="class: xl67, width: 42, bgcolor: transparent"]Original Score[/TD]
[TD="class: xl67, width: 45, bgcolor: transparent"]CoPTTM Score[/TD]
[TD="class: xl67, width: 37, bgcolor: transparent"]Rating[/TD]
[TD="class: xl67, width: 106, bgcolor: transparent"]CoPTTM Site Condition Rating[/TD]
[TD="class: xl67, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 44, bgcolor: transparent"]CoPTTM Score[/TD]
[TD="class: xl67, width: 37, bgcolor: transparent"]Rating[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl65, bgcolor: transparent"]High[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"]1 to 6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent"]6 to 11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: yellow"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]11[/TD]
[TD="class: xl65, bgcolor: transparent"]11 to 16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]Acceptable[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]51[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]16[/TD]
[TD="class: xl65, bgcolor: transparent"]16 to 21[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]21[/TD]
[TD="class: xl65, bgcolor: transparent"]21 to 26[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl65, bgcolor: transparent"]26 to 31[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]Needs Improvement[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31[/TD]
[TD="class: xl65, bgcolor: transparent"]31 to 36[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]36[/TD]
[TD="class: xl65, bgcolor: transparent"]36 to 41[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]41[/TD]
[TD="class: xl65, bgcolor: transparent"]41 to 51[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]51[/TD]
[TD="class: xl65, bgcolor: transparent"]51+[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent"]Dangerous[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*******[/TD]
[TD="class: xl65, bgcolor: transparent"]*******[/TD]
[TD="class: xl65, bgcolor: transparent"]******[/TD]
[TD="class: xl65, bgcolor: transparent"]***************************[/TD]
[TD="class: xl65, bgcolor: transparent"]**[/TD]
[TD="class: xl65, bgcolor: transparent"]*************[/TD]
[TD="class: xl65, bgcolor: transparent"]******[/TD]
[/TR]
</tbody>[/TABLE]

Formula for post #5 layout
Code: 
G2-> =IF(F2="","",LOOKUP(F2,--LEFT(SUBSTITUTE($B$2:$B$12,"-"," "),2),$C$2:$C$12))


Markmzz
 
Upvote 0
markmzz said:
Another way (with your table):

Layout

[TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl63, width: 37, bgcolor: transparent"]Table[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 72, bgcolor: transparent"]Original Score[/TD]
[TD="class: xl63, width: 52, bgcolor: transparent"]Rating[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]0[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1-5[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]6-10[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]11-15[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]16-20[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]21-25[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]39[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]26-30[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]31-35[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]36-40[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]41-50[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]51+[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]******[/TD]
[TD="class: xl63, bgcolor: transparent"]******[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]*************[/TD]
[TD="class: xl63, bgcolor: transparent"]*********[/TD]
[/TR]
</tbody>[/TABLE]


Formula

Code: 
G2-> =IF($F2="","",LOOKUP($F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))

Markmzz
Click to expand...

I got this when I tried it
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]Table[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Original Score[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Rating[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]10[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]26[/TD]
[TD="class: xl66, width: 64"]G2-> =IF($F2="","",LOOKUP($F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent, align: right"]1-5[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]9[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]18[/TD]
[TD="class: xl66, width: 64"]6[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent, align: right"]6-10[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]8[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]11-15[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]7[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]51[/TD]
[TD="class: xl66, width: 64"]0[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]16-20[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]6[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]35[/TD]
[TD="class: xl66, width: 64"]3[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]21-25[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]5[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]39[/TD]
[TD="class: xl66, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]26-30[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]4[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]50[/TD]
[TD="class: xl66, width: 64"]1[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]31-35[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]3[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]0[/TD]
[TD="class: xl66, width: 64"]10[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]36-40[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]3[/TD]
[TD="class: xl66, width: 64"]9[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]41-50[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]8[/TD]
[TD="class: xl66, width: 64"]8[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]51+[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]0[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]12[/TD]
[TD="class: xl66, width: 64"]7[/TD]
[/TR]
</tbody>[/TABLE]


The code just appeared in cell G2, I assume thats where it should be going... just couldn't get it to work. But looked like it would have been simpler.
 
Last edited:
Upvote 0
Mettaya1 said:
I got this when I tried it
[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]Table
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Original Score
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent"]Rating
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]10
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]26
[/TD]
[TD="class: xl66, width: 64"] G2-> =IF($F2="","",LOOKUP($F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent, align: right"]1-5
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]9
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]18
[/TD]
[TD="class: xl66, width: 64"]6
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: transparent, align: right"]6-10
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]8
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl68, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl69, width: 64, bgcolor: transparent, align: right"]11-15
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]7
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]51
[/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]16-20
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]6
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]35
[/TD]
[TD="class: xl66, width: 64"]3
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]21-25
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]5
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]39
[/TD]
[TD="class: xl66, width: 64"]2
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]26-30
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]4
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]50
[/TD]
[TD="class: xl66, width: 64"]1
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]31-35
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]3
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl66, width: 64"]10
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]36-40
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]2
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]3
[/TD]
[TD="class: xl66, width: 64"]9
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]41-50
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]8
[/TD]
[TD="class: xl66, width: 64"]8
[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64, bgcolor: transparent"]51+
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]12
[/TD]
[TD="class: xl66, width: 64"]7
[/TD]
[/TR]
</tbody>[/TABLE]


The code just appeared in cell G2, I assume thats where it should be going... just couldn't get it to work. But looked like it would have been simpler.
Click to expand...

Try only this:

=IF($F2="","",LOOKUP($F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))

PS: without G2->

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

wizau
  • Solved
Nested IF statements, or a better way?
Replies
4
Views
245
wizau
wizau
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
Z
  • Question Question
Massive Nested IF Statement Help Needed
Replies
1
Views
366
Zutalores
Z
A
  • Question Question
Attempting to auto generate ID names based on uncertain parameters
Replies
1
Views
232
Rijnsent
Rijnsent
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
247
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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