find lowest number and return it's adjacent cell
 

I need to find the lowest number in a column and return the value in the same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want a
formula to
Data4 4 return text 'Data1' in a cell I specify since '1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but ran
into the no
Data6 6 more than 7 nested functions issue. Can anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10

find lowest number and return it's adjacent cell
 

=INDEX(A2:A100,MATCH(MIN(B2:B100),B2:B100,0))


note that if you have more than one lowest number the above formula will
return the first occurrence

--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
I need to find the lowest number in a column and return the value in the
same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a
formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want a
formula to
Data4 4 return text 'Data1' in a cell I specify since
'1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but
ran
into the no
Data6 6 more than 7 nested functions issue. Can anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10

find lowest number and return it's adjacent cell
 

Ttry this:
=INDEX(A1:A10,MATCH(MIN(B1:B10),B1:B10,0))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Erik" wrote in message
...
I need to find the lowest number in a column and return the value in the
same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a
formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want a
formula to
Data4 4 return text 'Data1' in a cell I specify since
'1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but
ran
into the no
Data6 6 more than 7 nested functions issue. Can anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10

find lowest number and return it's adjacent cell
 

Box replies worked perfectly, thanks for the help.

"Ron Coderre" wrote:

Ttry this:
=INDEX(A1:A10,MATCH(MIN(B1:B10),B1:B10,0))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"Erik" wrote in message
...
I need to find the lowest number in a column and return the value in the
same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a
formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want a
formula to
Data4 4 return text 'Data1' in a cell I specify since
'1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but
ran
into the no
Data6 6 more than 7 nested functions issue. Can anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10

find lowest number and return it's adjacent cell
 

Ran into a problem. I need to ignore cells with Zero in them.

"Peo Sjoblom" wrote:

=INDEX(A2:A100,MATCH(MIN(B2:B100),B2:B100,0))


note that if you have more than one lowest number the above formula will
return the first occurrence

--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
I need to find the lowest number in a column and return the value in the
same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a
formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want a
formula to
Data4 4 return text 'Data1' in a cell I specify since
'1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but
ran
into the no
Data6 6 more than 7 nested functions issue. Can anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10

find lowest number and return it's adjacent cell
 

Try this

=INDEX(A2:A100,MATCH(MIN(IF(B2:B100<0,B2:B100)),B 2:B100,0))


note that it needs to be entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
Ran into a problem. I need to ignore cells with Zero in them.

"Peo Sjoblom" wrote:

=INDEX(A2:A100,MATCH(MIN(B2:B100),B2:B100,0))


note that if you have more than one lowest number the above formula will
return the first occurrence

--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
I need to find the lowest number in a column and return the value in the
same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a
formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want
a
formula to
Data4 4 return text 'Data1' in a cell I specify since
'1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but
ran
into the no
Data6 6 more than 7 nested functions issue. Can
anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10

find lowest number and return it's adjacent cell
 

Not sure what I'm doing wrong, the formula below gives the result of the
first cell with Zero in it. I have tried to cntl + shift and enter but it
doesn't work. Do I need to copy the formula differntly or something?

"Peo Sjoblom" wrote:

Try this

=INDEX(A2:A100,MATCH(MIN(IF(B2:B100<0,B2:B100)),B 2:B100,0))


note that it needs to be entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
Ran into a problem. I need to ignore cells with Zero in them.

"Peo Sjoblom" wrote:

=INDEX(A2:A100,MATCH(MIN(B2:B100),B2:B100,0))


note that if you have more than one lowest number the above formula will
return the first occurrence

--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
I need to find the lowest number in a column and return the value in the
same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a
formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want
a
formula to
Data4 4 return text 'Data1' in a cell I specify since
'1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but
ran
into the no
Data6 6 more than 7 nested functions issue. Can
anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10

find lowest number and return it's adjacent cell
 

Nevermind, I figured out what I was doing wrong. This worked I just needed
to type it fully into the cell and then do the cntl, shift, enter. I was
trying to copy the formula. Thanks.

"Erik" wrote:

Not sure what I'm doing wrong, the formula below gives the result of the
first cell with Zero in it. I have tried to cntl + shift and enter but it
doesn't work. Do I need to copy the formula differntly or something?

"Peo Sjoblom" wrote:

Try this

=INDEX(A2:A100,MATCH(MIN(IF(B2:B100<0,B2:B100)),B 2:B100,0))


note that it needs to be entered with ctrl + shift & enter


--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
Ran into a problem. I need to ignore cells with Zero in them.

"Peo Sjoblom" wrote:

=INDEX(A2:A100,MATCH(MIN(B2:B100),B2:B100,0))


note that if you have more than one lowest number the above formula will
return the first occurrence

--


Regards,


Peo Sjoblom



"Erik" wrote in message
...
I need to find the lowest number in a column and return the value in the
same
row but different column. Example.
A B
Data1 1 Column B numbers will change and I need a
formula
to find the
Data2 2 minimum number in column 'B' then return the
correct row in
Data3 3 column 'A.' So for this problem I would want
a
formula to
Data4 4 return text 'Data1' in a cell I specify since
'1'
is lowest #in 'B'
Data5 5 formula then used a seperate 'IF' formula but
ran
into the no
Data6 6 more than 7 nested functions issue. Can
anyone
help on this?
Data7 7
Data8 8
Data9 9
Data10 10