Figure 1: Index Match approximate
In the example above, MATCH and INDEX are used to recover a grade from a particular table depending on a given score value. For this is to be achieved, there is a need for an “approximate match”, as the main score doesn’t exist among the values displayed within the cells. The formula for the above logical expression in cell F9 is:
This returns “D” as the correct grade matching a score of 52.
How the Basic INDEX MATCH approximate formula works
This formula uses the MATCH function to find the appropriate row for a given value obtained. MATCH is designed to seek values within the range of the value in E9 with those in column B.
And so, the formula
Observe that the latter argument is 1, this means that it is equivalent to being ‘TRUE’ which permits MATCH to carry out an estimated match on scores listed in a climbing order. With this design, MATCH places the p first value on a position that is equal to or less than that of the value being sort for.
From the above example, the score sort for is 52 and so it falls within the range of the value in row B9
Whenever MATCH returns row B9 we have:
This, therefore causes INDEX to retrieve the value at row B9 of the range C5:C9 as a “D” grade.
- Always make sure that values in column B where the matching is compared with are arranged in ascending order, this will help MATCH to return the correction position and grade.