» 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:
- Keys to Reading an Annual Report (Barron's Business Keys)
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Advanced modelling in finance using Excel and VBA
- Analyzing Markets, Products, and Marketing Plans
- Microsoft Access Version 2002 Step by Step
- Understanding Financial Statements
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