Let's use a small sample and see how this works.

.......A.....B
1....1........
2....2........
3...........3
4...........4
5....5.......
6....6.......

=MIN(IF(A1:A6MAX(B1:B6),A1:A6))

This portion of the formula will return an array of either TRUE or FALSE:

A1:A6MAX(B1:B6)

We're comparing the values in col A to the MAX value of col B. So:

MAX(B1:B6) = 4

A14 = FALSE
A24 = FALSE
A34 = FALSE
A44 = FALSE
A54 = TRUE
A64 = TRUE

Where this condition is TRUE the corresponding numbers from col A are then
passed to the MIN function:

MIN({FALSE;FALSE;FALSE;FALSE;5;6)

MIN ignores the logical FALSE and returns the smallest number of the array.

So, the final result of the formula is 5.

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
Bill, you're a star. Do you have the time to explain how this formula
works?
I'm not experienced with arrays.

"T. Valko" wrote:

One way...

Try this array formula** :

=MIN(IF(A2:A10MAX(B2:B10),A2:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
I have tried doing this with INDEX and MATCH but can't get my head round
it.

I have 2 columns of data, containing blanks that are the result of an
IF
formula.

40
100
100
200
400
600
800
1200
1600

The values will always be sorted from low to high. What I need to do is
take
the max value from column B (400 in this case) and return the next
highest
value from column A (600).

Grateful for any pointers on this.