Basic INDEX MATCH approximate

General formula 

=INDEX(grades,MATCH(score,scores,1))

Example

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:

=INDEX(C7:C12,MATCH(E9,B7:B12,1))

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

=MATCH(E9,B7:B12,1)

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:

=INDEX(C5:C9,B9)

This, therefore causes INDEX to retrieve the value at row B9 of the range C5:C9 as a “D” grade.

Note

  • 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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar