displaying adjacent cell when using max/min formulae

[formula428]

I'm using the max/min formulas to display the appropriate values [say in
column A]. say the maximum is located in cell A20, i'd also like to have
excel display the output of cell B20 in another cell.

how do i do this in Excel 2003?

 

[Mike H]

Hi,

=VLOOKUP(MAX(A1:A50),A1:B50,2,FALSE)

Mike

 

[Peo Sjoblom]

=INDEX(B:B,MATCH(MAX(A:A),A:A,0))

--


Regards,


Peo Sjoblom

 

[formula428]

Does it matter if the "output cell" is text?

Maybe I should give the actual columns in the example.

I am doing max/min for column E. I want excel to give me the corresponding
text of column C. Therefore, if the max is E356, I want (in another cell)
Excel to display C356 which is text, not a value.

Hi,

=VLOOKUP(MAX(A1:A50),A1:B50,2,FALSE)

Mike

I'm using the max/min formulas to display the appropriate values [say in
column A]. say the maximum is located in cell A20, i'd also like to have
excel display the output of cell B20 in another cell.

how do i do this in Excel 2003?

 

[ShaneDevenshire]

Hi,

you could use

=VLOOKUP(MAX(A1:A24),A1:B24,2,)

 

[Mike H]

Use the index function suggested by Peo

Does it matter if the "output cell" is text?

Maybe I should give the actual columns in the example.

I am doing max/min for column E. I want excel to give me the corresponding
text of column C. Therefore, if the max is E356, I want (in another cell)
Excel to display C356 which is text, not a value.

Hi,

=VLOOKUP(MAX(A1:A50),A1:B50,2,FALSE)

Mike

I'm using the max/min formulas to display the appropriate values [say in
column A]. say the maximum is located in cell A20, i'd also like to have
excel display the output of cell B20 in another cell.

how do i do this in Excel 2003?

 

[formula428]

I couldn't get VLOOKUP to work, but index worked great! thanks!

 

[ShaneDevenshire]

Hi,

My initial response was to your first posting, when I revisited you actual
layout, given in the 2nd posting, you are correct, VLOOKUP won't work because
it needs to check the left hand column and return data in that column or one
to the right. And you are look in column E and wanting to return something
from column C. However, you can do that with a related function, LOOKUP,
provided your data is ordered, which it probalby not the case:

=LOOKUP(MAX(E1:E400),E1:E400,C1:C400)

but this only works if column E is sorted ascending. So I think you should
use the MATCH, OFFSET or INDEX combinations.