Matching & Indexing formula for rating actual against target based on a scale

Alice149

I'm trying to write a formula for Balanced score card assessment to award a 'rating' against a target that then translates to points and based on the points how much bonus is received. I'm writing the formula as below:

=IF([2022 Actual]6 = 0, 0, IF([2022 Actual]6 < [Scale 2]6, [Scale 2]8, IF([2022 Actual]6 >= [Scale 4]6, [Scale 4]8, INDEX([Scale 2]8:[Scale 4]8, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6)) + (([2022 Actual]6 - INDEX([Scale 2]6:[Scale 4]6, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6))) / (INDEX([Scale 2]6:[Scale 4]6, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6) + 1) - INDEX([Scale 2]6:[Scale 4]6, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6))) * (INDEX([Scale 2]8:[Scale 4]8, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6) + 1) - INDEX([Scale 2]8:[Scale 4]8, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6))))))) * 100

This formula is working if the actual figure is above the target figure but when it's below the formula shows as invalid (screenshots below) help! :)

Garrett Henke

@Alice149 One thing that jumps out at me is that you are not looking for exact matches within your index match formulas. To find the exact match you need to add ,0 at the end of the match function. This could be the reason, there could be a different error occurring but try this first as a starting point.

=IF([2022 Actual]6 = 0, 0, IF([2022 Actual]6 < [Scale 2]6, [Scale 2]8, IF([2022 Actual]6 >= [Scale 4]6, [Scale 4]8, INDEX([Scale 2]8:[Scale 4]8, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6, 0)) + (([2022 Actual]6 - INDEX([Scale 2]6:[Scale 4]6, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6, 0))) / (INDEX([Scale 2]6:[Scale 4]6, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6, 0) + 1) - INDEX([Scale 2]6:[Scale 4]6, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6, 0))) * (INDEX([Scale 2]8:[Scale 4]8, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6, 0) + 1) - INDEX([Scale 2]8:[Scale 4]8, MATCH([2022 Actual]6, [Scale 2]6:[Scale 4]6, 0))))))) * 100

If that doesn't work can you provide a breakdown of where each value is tied to? It's hard to see exactly what value goes where without knowing the column headers and row numbers.

Alice149

@Garrett Henke Thanks so much for your help - so your formula works when I put 16 in the actual column but not 20, the error is #nomatch. Definitely a step in the right direction! I don't think I want exact matches as actual could be any number so it needs to operate like a sliding scale right?

Frustratingly I can only highlight the cells I'm working with as if I show the formula at the same time it covers the cells - here are some more screenshots that show the columns and rows. Thanks!

And screenshot of the No match result

Garrett Henke

@Alice149 after building your sheet and looking through the formula I think I have found your problem. You are index matching incorrectly by trying to use it as a horizontal vlookup.

Transpose your scales so that Achievement, Target Multiplier, and Rating are the columns instead of having them as rows.

Then when you index match you will need to change the index range from horizontal to vertical and then do the same for the match range. This should fix the index match issue. I know this is a complete rebuild of your sheet but for it to work the way you want it is necessary.

Alice149

@Garrett Henke - Thanks, I tried it again having made the rows into columns and am now getting unpassable in the formula bar. Thank you for all your help so far - do you think you could help untangle my formula one more time and hopefully this works! :)

=IF(2022 Actual6=0,0,IF(2022 Actual6<Achievement6,[Rating Scale]6,IF(2022 Actual6>=Achievement8,[Rating Scale]8,INDEX(Rating Scale6:Rating Scale8,MATCH(2022 Actual6,Achievement6:Achievement8))+((2022 Actual6-INDEX(Achievement6:Achievement8,MATCH(2022 Actual6,Achievement6:Achievement8)))/(@INDEX(Achievement6:Achievement8,MATCH(2022 Actual6,Achievement6:Achievement8)+1)-INDEX(Achievement6:Achievement8,MATCH(2022 Actual6,Achievement6:Achievement8)))*(@INDEX([Rating Scale]6:[Rating Scale]8,MATCH(2022 Actual6,Achievement6:Achievement8)+1)-INDEX([Rating Scale]6:[Rating Scale]8,MATCH(2022 Actual6,Achievement6:Achievement8))))))*100)

Bbs

@Alice149

i guess this is the result u are expecting. To avoid writing a long formula, calculate the slope in the table to the right itself when actual falls between the range 16.4 to 28.68

i have shown the result for different actual 2022 values in case 1 to case 6 using the same formula in D2 cell

also use vlookup. It give the max number less than or equal to lookup value

vlookup example: vlookup(lookup, lookup array, column index, match type approx or exact match)

example with respect to case 3, lookup value is 20, lookup array is achievement to slope table, column index is column number in the table range selected for rating scale, match type = 1 for approximate match