IF and MAX functions

F

FnOzzY

I have a database that I would like to find the MAX number in a range of
cells then return the value of a different cell in the row with the MAX
number. How do I do that?
 
J

JulieD

Hi

try
=LOOKUP(MAX(J2:J6),J2:J6,D2:D6)

where J2:J6 is the range of where you want to find the max in and D2:D6 is
the column with the actual value that you want to return.

Hope this helps
Cheers
JulieD
 
M

Myrna Larson

If the max is in column C and you want to return the corresponding value from
column A, and there are no duplicates

=INDEX(A:A,MATCH(MAX(C:C),C:C,0))
 
M

Myrna Larson

Or, if you want to return data from a column to the right of the once with the
max value, you can use VLOOKUP:

=VLOOKUP(MAX(C:C),C:F,4,0)
 
F

FnOzzY

Thank you so much. Worked great.

JulieD said:
Hi

try
=LOOKUP(MAX(J2:J6),J2:J6,D2:D6)

where J2:J6 is the range of where you want to find the max in and D2:D6 is
the column with the actual value that you want to return.

Hope this helps
Cheers
JulieD
Click to expand...
 
M

Myrna Larson

Hi, Julie:

Am I correct in that LOOKUP requires the first range to be sorted ascending?
If so, I don't know if that criterion is fulfilled with the OP's data.

Myrna Larson
 
M

Myrna Larson

Addendum to my previous question. As long as you are looking for the maximum
value, the range doesn't need to be sorted, but if you're looking for some
other value, it may not work as desired.
 
Top