A common challenge that office workers face is to assign predefined categories to some items based on minimum and maximum numbers. A simple example is to assign grades (e.g., Outstanding, Excellent, Very Good, etc.) to a list of students based on where each student’s mark falls. Each grade category represents a min-max range as shown below:
| Min % Grade | Max % Grade | Grade |
| 0% | 59% | Fail |
| 60% | 69% | Passing Below Average |
| 70% | 79% | Satisfactory |
| 80% | 84% | Good |
| 85% | 89% | Very Good |
| 90% | 94% | Excellent |
| 95% | 100% | Outstanding |
One could argue that this task can be done using the “IFS” function. That is true if the categories are few, but not when you have tens of categories. Another more complex example is categorizing P&L (Profit & Loss) Statement line items based on GL Account numbers where you might have 2000+ GL accounts that must be categorized into 50+ P&L line items.
In both scenarios the trick is that finding the right category for each item is not a direct lookup. It is based on comparison of values to each combination of min-max range. Hence, the combination of INDEX and XMATCH functions becomes very handy.
Let’s first revisit INDEX and XMATCH functions separately.
INDEX returns a value or the reference to a value from within a table or range. In our example, INDEX(‘Grade’,3) = “Satisfactory”. Here, INDEX returns the item with the third position from the Grade column (array).
XMATCH returns the relative position of an item in an array. For instance, if we have an array (A) of four logical values A = (0,0,1,0), and we apply the function XMATCH(1, A) the result will be 3, which is the position of value 1 in array A.
Since Excel stores a TRUE as 1 and FALSE as 0, we can have a condition in the XMATCH function that produces a logical array where it gives the position of the Grade where the student’s mark meets two conditions at the same time: above Min % Grade and below Max % grade. In other words, a multiplication of TRUE x TRUE =1. Then, with the help of INDEX function the Grade description will be looked up based on the unique position of the product (1). The above gives you the key to solving this problem. Try it and write a formula that assigns Grade category to a list of students. Then, watch the video to see my solution.
