» Retrieving the First Value in a List that is Greater / Smaller than a Specified Number
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
We want to use each of the numbers in column C as criteria when searching through List1 (column A). For each search, we want to retrieve the first number from the list that is greater than the current criteria, and also the first number that is smaller.
Solution:
Use the INDEX and MATCH functions as shown in the following Array formulas:
First greater value - {=INDEX($A$2:$A$7,MATCH(TRUE,$A$2:$A$7>C2,0))}
First smaller value - {=INDEX($A$2:$A$7,MATCH(FALSE,$A$2:$A$7>C2,0))}
To apply Array formula:
Select the cell, press
Book Store:
Recommended Books:
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- Financial Risk Manager Handbook, Second Edition
- Microsoft Excel 2002 Visual Basic for Applications Step by Step
- The Essential 55: An Award-Winning Educator's Rules for Discovering the Successful Student in Every Child
- Windows XP for Dummies
- The Total Money Makeover. : A Proven Plan for Financial Fitness
No comments have been submitted.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
