Pulling information from a list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smck - 26 Jun 2006 03:09 GMT

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.

Reply to this Message

Biff - 26 Jun 2006 03:18 GMT

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

>I have a list of sales persons (the list varies from time to time), and
>want
[quoted text clipped - 15 lines]
>
> Thanks in advance.

Reply to this Message

Max - 26 Jun 2006 03:20 GMT

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))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 13 lines]
>
> Thanks in advance.

Reply to this Message

Aladin Akyurek - 26 Jun 2006 06:25 GMT

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.

Reply to this Message