Thread: Retrieving the First Value in a List that is Greater / Smaller than a Specified Number

I have a problem with a counting formula.

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

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

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!