Scorecard table with ranged target scores

[jarrgonn]

Hello,

I've been trying to use IF and INDEX/MATCH for my issue but to no avail. I honestly need to use INDEX/MATCH for this problem since the real productivity table has a couple of columns in between.

The target scores are ranged; I need the ACTUAL PROD TABLE to convert to single digit scores on the SCORECARD TABLE based on the targets in the SCORECARD GUIDE TABLE. Please see attached.

Thank you!

[Bo_Ry]

Please try at G3

=INDEX($D$3:$D$8,MATCH(O3,TEXT(RIGHT($C$3:$C$8,2),"0;;;""999""")/(LOOKUP(ROW($B$3:$B$8),ROW($B$3:$B$8)/($B$3:$B$8>""),$B$3:$B$8)=G$2),-1))

or set scorecard guide as in Sheet2
G3
=LOOKUP(O3,$C$3:$C$8/($B$3:$B$8=G$2),$D$3:$D$8)

[jarrgonn]

Thanks for this.

But can INDEX/MATCH be an alternative for the LOOKUP/ROW combo formula instead? Coz I also need the dates to be included in the equation as well - first the date gets looked up, then the HT or LT after.

[Bo_Ry]

Yes, please try at G3

=INDEX($D$3:$D$8,MATCH(INDEX($O$3:$T$8,MATCH($F3,$N$3:$N$8,),MATCH(LOOKUP(9^9,$G$1:G$1),$O$1:$T$1,)+(G$2="LT")),TEXT(RIGHT($C$3:$C$8,2),"0;;;""999""")/(LOOKUP(ROW($B$3:$B$8),ROW($B$3:$B$8)/($B$3:$B$8>""),$B$3:$B$8)=G$2),-1))

[jarrgonn]

Hello, Bo_Ry.

Thanks for the help.

I just recently started using your formula/table and tried adjusting it to my data but it's somehow messing up. On the file attached, SAMPLE SHEET tab shows the desired "look" of the score table I'm trying to achieve while CURRENT FORMULA USED tab is where I used the formula you suggested but - if you'd look at the scores on that sheet - it's not showing the accurate scores.

I've been trying to work around this for days now (during my free time at work) but to no avail. I hope you can help me once again with this.

Thank you.

[JeteMc]

Try pasting the following modification of Bo_Ry's array entered formula into cell B17 and then, after activating by simultaneously pressing the Ctrl, Shift and Enter keys, copy over and down to cell I24:

=IF(INDEX($C$4:$FA$11,MATCH($A17,$A$4:$A$11,),MATCH(LOOKUP(9^9,$B$15:B$15),$C$2:$FA$2,)+(B$16="LT")*3)="","",INDEX('Scorecard guide'!$D$3:$D$8,MATCH(INDEX($C$4:$FA$11,MATCH($A17,$A$4:$A$11,),MATCH(LOOKUP(9^9,$B$15:B$15),$C$2:$FA$2,)+(B$16="LT")*3),TEXT(RIGHT('Scorecard guide'!$C$3:$C$8,2),"0;;;""999""")/(LOOKUP(ROW('Scorecard guide'!$B$3:$B$8),ROW('Scorecard guide'!$B$3:$B$8)/('Scorecard guide'!$B$3:$B$8>""),'Scorecard guide'!$B$3:$B$8)=B$16),-1)))

Let us know if you have any questions.

[jarrgonn]

Try pasting the following modification of Bo_Ry's array entered formula into cell B17 and then, after activating by simultaneously pressing the Ctrl, Shift and Enter keys, copy over and down to cell I24:

=IF(INDEX($C$4:$FA$11,MATCH($A17,$A$4:$A$11,),MATCH(LOOKUP(9^9,$B$15:B$15),$C$2:$FA$2,)+(B$16="LT")*3)="","",INDEX('Scorecard guide'!$D$3:$D$8,MATCH(INDEX($C$4:$FA$11,MATCH($A17,$A$4:$A$11,),MATCH(LOOKUP(9^9,$B$15:B$15),$C$2:$FA$2,)+(B$16="LT")*3),TEXT(RIGHT('Scorecard guide'!$C$3:$C$8,2),"0;;;""999""")/(LOOKUP(ROW('Scorecard guide'!$B$3:$B$8),ROW('Scorecard guide'!$B$3:$B$8)/('Scorecard guide'!$B$3:$B$8>""),'Scorecard guide'!$B$3:$B$8)=B$16),-1)))

Let us know if you have any questions.

Thank you very much!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.