Index Match multiple criteria with a `smaller than` criteria without arrays

Question

I have a list of data that is separated by Date, Team and their Category Score. Category Score changes for each Team on a daily basis and based on their actual score of the day, I'll need to identify the Category Score that they fall into.

For example, if on the 1st Jan 2018, Team A's score is 225. Team A's score whole fall in between 200 and 250. The assigned Score Category will then be rounded up to the last category which will be 200

However, the next day, Team A's Score Category is changed. The 225 score nicely rests on a Score Category and thus the assigned category will be 225.

Hopefully that gives a clear picture.

To help with the matter, the data is always sorted by Date and Team with ascending Score Category

I'm looking for an excel formula that hopefully does not use the array function. I've so far looked at the Index Match formulas but they were all exact match that does not allow me to approximate to a value.

For e.g.

Date        Team    Score Category
1/1/2018    A       100
1/1/2018    A       150
1/1/2018    A       200
1/1/2018    A       250
1/1/2018    A       300
1/1/2018    B       300
1/1/2018    B       400
1/1/2018    B       500
2/1/2018    A       150
2/1/2018    A       200
2/1/2018    A       225
2/1/2018    A       300
2/1/2018    A       350
2/1/2018    B       350
2/1/2018    B       450
2/1/2018    B       550

Date:                       1/1/2018
Team:                       A
Actual Score:               225
Category Score (Output):    200


Date:                       2/1/2018
Team:                       A
Actual Score:               225
Category Score (Output):    225

Answer 1

Try this array formula:

= INDEX($C$2:$C$17,MATCH(B21,IF(($A$2:$A$17=B19)*($B$2:$B$17=B20),$C$2:$C$17),1))

Note this is an array formula, must be entered with Ctrl+Shift+Enter on your keyboard rather than just Enter.

See below, working example with your data. I also copied this cell from B22 to B27.

A few notes:

This formula only works if your Score Categories for a particular Date and Team combination are in ascending order (because using a third argument of 1 in MATCH requires that the data be sorted), but from the way your question is worded, it doesn't sound like this will be an issue.

The formula would probably also break if your Score Categories and/or Actual Scores are negative, since the IF statement effectively "zero's-out" the data you're not interested in for this particular Date and Team combination. But if the Actual Score is negative then it probably would erroneously consider one of these 0's as a match. This is just a guess, I didn't test this though.

Another thing to consider is to add a Score Category of 0 to the top of each Date and Team combination. I say this because if for example the Actual Score is lower than the lowest Score Category (e.g. if the Actual Score is 50 but the lowest Score Category is 100), then a match won't be found, and I'm guessing the formula would return an error.