» Retrieving Grade Value for Corresponding Mark

CATEGORY - Lookup Formulas VERSION - All Microsoft Excel Versions

Problem:

Column B contains the mark matching each of the grades shown in column A.
We want to grade each mark in column D by finding the mark closest to it in column B, and then retrieving the corresponding grade from column A.

Solution:

Use the INDEX, MIN, IF, ABS, and ROW functions as shown in the following Array formula:
{=INDEX($A$2:$A$6,MIN(IF(ABS($B$2:$B$6-D2)=MIN(ABS($B$2:$B$6-D2)),ROW($B$2:$B$6)-ROW($B$2)+1)),1)}

To apply Array formula:
Select the cell, press and simultaneously press .