I try to re-word my description
In cell A1, there is 144. I would like to find the closest values within the
range in col B, then return the different 0 in cell C1
In cell A2, there is 133. I would like to find the closest values within the
range in col B, then return the different 1 in cell C2
In cell A3, there is 130. I would like to find the closest values within the
range in col B, then return the different 4 in cell C3
In cell A4, there is 126. I would like to find the closest values within the
range in col B, then return the different 0 in cell C4
After that, I would like to find the minimum value under column C and return
the maximum value 0 under column A 144.

Does anyone have any suggestions?
Thanks everyone very much for any suggestions
Eric

"T. Valko" wrote:

I would like to determine the closest and
maximum numbers between A and B
A B
144 134
133 126
130 144
126 138
It should return 144 in cell C1

The closest range is 133 126 and the max value of that range is 133.

Try these array formulas** :

For the MAX:

=MAX(INDEX(A2:B5,MATCH(TRUE,ABS(A2:A5-B2:B5)=MIN(ABS(A2:A5-B2:B5)),0),0))

For the MIN:

=MIN(INDEX(A2:B5,MATCH(TRUE,ABS(A2:A5-B2:B5)=MIN(ABS(A2:A5-B2:B5)),0),0))

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


--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
I try to re-word my statement
There are two lists of number under column A and B.
Example 1:
I would like to determine the closest and minimum numbers between A and B

A B
173 152
160 156
156 173
150 162
It should return 156 in cell C1

Example 2:
I would like to determine the closest and maximum numbers between A and B

A B
144 134
133 126
130 144
126 138
It should return 144 in cell C1

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

"Jarek Kujawa" wrote:

yr formula gives required result
=MAX(IF(A1:A4-B1:B4=MIN(A1:A4-B1:B4),A1:A4)) should give 37


one way to achieve what you want is:
=LARGE(IF($A$1:$A$4-$B$1:$B$4=MAX($A$1:$A$4-$B$1:$B$4),$A$1:$A$4),1)
=LARGE(IF($A$1:$A$4-$B$1:$B$4=MAX($A$1:$A$4-$B$1:$B$4),$A$1:$A$4),2)
....
gives 54, 48...