Find a cell greater than another cell

I'm sure there's an easy way, but my brain isn't thinking of it.

I have an array of 4 cells: A1:D1

22

43

34

99

I want to get the next highest value than A2. So I tried

=INDEX(A1:D1,MATCH(A2,A1:D1,-1))

But that just returns an error. I expected it to return 99.

If I wanted to point to A3 instead, I would expect to get 43.

max(A1:D1,a2)?

hmm actually i dont get how a2,a3 fits into your question

-1 --> MATCH finds the smallest value that is greater than or equal tolookup_value.

The values in the lookup_array argument must be placed in descending order,

for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

=MIN(IF($A$1:$D$1>A2,$A$1:$D$1)) --> array formula(Ctrl+Shift+Enter)

Attachment 525053

Try:
=LOOKUP(2,1/(IF($A$1:$D$1-A2>0,$A$1:$D$1-A2,"")=MIN(IF($A$1:$D$1-A2>0,$A$1:$D$1-A2,""))),$A$1:$D$1)
Drag down for A3 criteria

Sorry, yes, I meant B1, and C1, not A2, A3. My appologies.

Chief_abound, your answer works great. Thanks. Bebo, thanks for the answer. Chief_abound's works better for me, but thanks anyway Bebo.

Phuocam, yours is also good, and doesn't require ctrl+shift+enter... I might use that instead.

Thanks everyone.

Another way.

Formula:

Please Login or Register  to view this content.