1

I input the number 3.

I want to find the first number in a 3x3 table for example, that is bigger than my 3.

2   3   4
5   6   7
8   5   6

In this case, I want this formula to return 4.

2
  • How can I know which is the first number? for column or row? – Lee May 3 at 7:55
  • When you say "first", do you mean the smallest number which is bigger than 3? – AdamV May 5 at 12:51
1

You need this Array (CSE) formula:

  • I'm assuming that you have data in Range A1:C3.

    {=MIN(IF($A$1:$C$3>3,$A$1:$C$3))}

  • Finish formula with Ctrl+Shift+Enter.

  • Adjust cell references in the formula as needed.

3
  • 1
    +1 - for current versions of Excel, you don't need to finish the formula with Ctrl+Shift+Enter. Enter will do. – FlexYourData May 3 at 14:21
  • 1
    @FlexYourData,, thanks for encouragement,, my experience says it's is wise step to work with an Array method since formula has to check cell by cell for the value in entire range,,, useful when handling huge range ☺ – Rajesh S May 4 at 5:40
  • Glad to help you @Bogdan ,, keep asking ☺ – Rajesh S May 4 at 5:41
0

For any size table:

=AGGREGATE(15,6,myRange*1/(myRange>3),1)

where myRange is the cell reference to your table.

Not the answer you're looking for? Browse other questions tagged or ask your own question.