MAX Function

D

DaveB

Instead of 300 showing up on line 4, i would like the
words "RANGERS". Can this be done?

Thanks. Daveb


PHILLIES 100
BREWERS 200
RANGERS 300
=MAX(B1:B2) 300
 
C

Chip Pearson

Dave,

Assuming your data values are in A1:B10, the following formula
will return the value from column A corresponding to the maximum
value in column B.

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0))

Adjust the ranges to suit your needs.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
A

Aladin Akyurek

DaveB said:
Instead of 300 showing up on line 4, i would like the
words "RANGERS". Can this be done?

Thanks. Daveb


PHILLIES 100
BREWERS 200
RANGERS 300
=MAX(B1:B2) 300
Click to expand...

If DOWNERS has also a score of 300, you'll need to retieve both RANGERS
and DOWNERS. The following link describes a formula system which will
produce such a result list:

http://tinyurl.com/562xz
 
B

Bernd Plumhoff

If your Max value appears more than once, you might like
to use:

100 PHILLIES
200 BREWERS
300 RANGERS
200 BREWERS
300 OTHERS
=MAX(A1:A5) =vlookupall(A6,A1:B5,2)

where vlookupall() is defined as:

Option Explicit

Public Function vlookupall$(strSearch As String, rngRange
As Range, lngLookupCol As Long)

'Vlookupall searches in first column of rngRange for
strSearch and returns corresponding
'values of column lngLookupCol if strSearch was found. All
corr. values are collected and
'returned in one string (result of function).

Dim i As Long

If lngLookupCol > rngRange.Columns.Count Then
vlookupall = CVErr(xlErrValue)
Exit Function
End If

vlookupall = ""

For i = 1 To rngRange.Rows.Count

If rngRange(i, 1).Text = strSearch Then

vlookupall = vlookupall & rngRange(i,
lngLookupCol).Text & "; "

End If

Next i

If Right(vlookupall, 2) = "; " Then
vlookupall = Left(vlookupall, Len(vlookupall) - 2)
End If

End Function

HTH,
Bernd
 
Top