» Retrieving the First Value in a List that is Greater / Smaller than a Specified Number
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
Problem: 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:
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- The Intelligent Investor: The Classic Bestseller on Value Investing
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Analyzing Markets, Products, and Marketing Plans
- Fish! A Remarkable Way to Boost Morale and Improve Results
- The Ernst & Young Business Plan Guide
No comments have been submitted.
