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
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
One way
In A4: =INDEX(A1:A3,MATCH(MAX(B1:B3),B1:B3,0))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"DaveB" <[email protected]> wrote in message
news:[email protected]...
> 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
>
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
"DaveB" <[email protected]> wrote in message
news:[email protected]...
> 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
>
DaveB wrote:
> 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
>
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks