» 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
- Absolute Beginner's Guide to Microsoft Excel 2002
- Guide to Financial Reporting and Analysis
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Business Analysis and Valuation: Using Financial Statements, Text and Cases
- Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
Matching and adding
gegger
I need to match the same five numbers (1,2,3,4,5) in a column over and over and expose the goupings missing any in the string (1,2,4,5) and then insert a row or the number to expose where its missing... please help!
In column B, I have numbers from 1 to 40. In separate columns along side each number of the page. I would like to separate the numbers in groups of 3; 1,2,3 then 4,5,6 etc.
How can this be done?
I would be ever so grateful for some help.
Happy NEW YEAR
duse