How do I find the cell reference that contains the minimum or maximum

  • Thread starter Thread starter fadsnet
  • Start date Start date
F

fadsnet

Hello
I want to know how do I find the cell reference that contains the
minimum value between a range of cells.
I know I can use the function =min(A1:A100) but this returns the
minimum value.
I want to know where this minimum is.
And, if I know it's A50 how do I use the cell B50, let me explain: I
have some dates in B cells and some values in A cells. I want to know
when the minimum did occur (this should be a date in B cells)
Thanks a lot
 
Hi!

So, you want to find the MIN in col A and return the corresponding value
from col B?

Try this:

=INDEX(B1:B100,MATCH(MIN(A1:A100),A1:A100,0))

Note: if there are duplicate MIN's the formula will match the FIRST
instance.

Biff
 
fadsnet said:
Hello
I want to know how do I find the cell reference that contains the
minimum value between a range of cells.
I know I can use the function =min(A1:A100) but this returns the
minimum value.
I want to know where this minimum is.
And, if I know it's A50 how do I use the cell B50, let me explain: I
have some dates in B cells and some values in A cells. I want to know
when the minimum did occur (this should be a date in B cells)
Thanks a lot
Click to expand...


Try with this:

=ADDRESS(ROW()-1+MATCH(MIN(A9:A24),A9:A24,0),1,4)

In this example I have some random numbers in A9:A24 and the formula gives
the address of the cell with the minimum value.

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
Back
Top