» 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:
- The South Beach Diet: The Delicious, Doctor-Designed, Foolproof Plan for Fast and Healthy Weight Loss
- Microsoft Excel VBA Programming for the Absolute Beginner
- Word 2002: The Complete Reference
- Analysis of Financial Statements
- Microsoft Windows XP Step by Step (With CD-ROM)
- How to Read A Financial Report
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