» Retrieving Grade Value for Corresponding Mark
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- Advanced modelling in finance using Excel and VBA
- Microsoft Outlook 2002 for Dummies
- Special Edition Using Microsoft Excel 2002
- The Ernst & Young Business Plan Guide
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
- Word 2002: The Complete Reference
No comments have been submitted.
