Pulling information from a list

[smck]

I have a list of sales persons (the list varies from time to time), and want
to query which sales person has the highest or lowest sales. I would like to
get the return of the name and amount. Here is an example.

A B
John Jones 123
Mary JO 456
Luci Lu 6789
Homer Simpson 91

the result should return Homer Simpson 91 if I ask for the lowest and Luci
Lu 6789 if I ask for the highest.
I have tried using VLOOKUP and MATCH, MAX, MIN but not getting the desired
result. Maybe I am not using them in the correct syntax or maybe I am
choosing the wrong functions. Any help in this is greatly appreciated.

Thanks in advance.

[Biff]

Hi!

For the highest:

=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0))

For the lowest:

=INDEX(A1:A4,MATCH(MIN(B1:B4),B1:B4,0))

Neither formula accounts for the possibility of ties.

Biff

"smck" <smck@discussions.microsoft.com> wrote in message
news:B6369637-DCE6-485B-8778-0D681726FCCF@microsoft.com...
>I have a list of sales persons (the list varies from time to time), and
>want
> to query which sales person has the highest or lowest sales. I would like
> to
> get the return of the name and amount. Here is an example.
>
> A B
> John Jones 123
> Mary JO 456
> Luci Lu 6789
> Homer Simpson 91
>
> the result should return Homer Simpson 91 if I ask for the lowest and Luci
> Lu 6789 if I ask for the highest.
> I have tried using VLOOKUP and MATCH, MAX, MIN but not getting the desired
> result. Maybe I am not using them in the correct syntax or maybe I am
> choosing the wrong functions. Any help in this is greatly appreciated.
>
> Thanks in advance.

[Max]

Assuming no ties in max or min sales, try
Max: In say, D1: =INDEX(A:A,MATCH(MAX(B:B),B:B,0))
Min: In say, D2: =INDEX(A:A,MATCH(MIN(B:B),B:B,0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"smck" wrote:
> I have a list of sales persons (the list varies from time to time), and want
> to query which sales person has the highest or lowest sales. I would like to
> get the return of the name and amount. Here is an example.
>
> A B
> John Jones 123
> Mary JO 456
> Luci Lu 6789
> Homer Simpson 91
>
> the result should return Homer Simpson 91 if I ask for the lowest and Luci
> Lu 6789 if I ask for the highest.
> I have tried using VLOOKUP and MATCH, MAX, MIN but not getting the desired
> result. Maybe I am not using them in the correct syntax or maybe I am
> choosing the wrong functions. Any help in this is greatly appreciated.
>
> Thanks in advance.

[Aladin Akyurek]

Let A3:B8 house:

Sales Person,Sales
Jon Jones,123
Mary Jo,456
Lucy Lu,6789
Homer Simpson,91
Andy Capp,91

In D1 enter:

=MAX(B4:B8)

In D2 enter & copy to E2:

=COUNTIF($B$4:$B$8,D1)

In E1 enter:

=MIN(B4:B8)

In D3:E3 enter: Max Performer(s) and Min Performer(s)

D4:

=IF(ROWS(D$4:D4)<=D$2,INDEX($A$4:$A$8,
SMALL(IF($B$4:$B$8=D$1,ROW($B$4:$B$8)-ROW($B$4)+1),
ROWS(D$4:D4))),"")

which you need to confirm with control+shift+enter (not just with enter)
then copy across to E4 and down.

smck wrote:
> I have a list of sales persons (the list varies from time to time), and want
> to query which sales person has the highest or lowest sales. I would like to
> get the return of the name and amount. Here is an example.
>
> A B
> John Jones 123
> Mary JO 456
> Luci Lu 6789
> Homer Simpson 91
>
> the result should return Homer Simpson 91 if I ask for the lowest and Luci
> Lu 6789 if I ask for the highest.
> I have tried using VLOOKUP and MATCH, MAX, MIN but not getting the desired
> result. Maybe I am not using them in the correct syntax or maybe I am
> choosing the wrong functions. Any help in this is greatly appreciated.
>
> Thanks in advance.