Database question

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Sample database:

Name Zone Agency Month Amount
John Doe West Smith Jan 150,000
Bill Smith North Johnson Feb 175,000

Given a database like above with hundreds of entries, what array formula
would one use to find the following info:

1. Which person from the North Zone had the highest value sale in the month
of Jan?
2. Which person from the North Zone had the 2nd highest value sale in the
month of Jan?
How about the 3rd highest?

If you can give me the direction to go I think I can figure out how to
answer other similar questions. Thanks alot!!, Jim
 
One way (array-entered):

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="North")*$E$2:$E$1000,1),($
B$2:$B$1000="North")*$E$2:$E$1000,0))

Change the 1 in the LARGE() function to 2, 3, etc.
 
Thanks for the answer:

One way (array-entered):

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="North")*$E$2:$E$1000,1),($
B$2:$B$1000="North")*$E$2:$E$1000,0))


Change the 1 in the LARGE() function to 2, 3, etc.


But how do I limit to a second criteria: 2nd highest in the month of Jan
(column D)

Thanks
 
Postman Jim

Thanks for the answer but I didn't see how to limit to Jan or the month.
Thanks
 
Reread my answer, starting with "Change..."


Thanks for the answer:

One way (array-entered):

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="North")*$E$2:$E$1000,1),($
B$2:$B$1000="North")*$E$2:$E$1000,0))


Change the 1 in the LARGE() function to 2, 3, etc.


But how do I limit to a second criteria: 2nd highest in the month of Jan
(column D)

Thanks
Click to expand...
 
Yes I understand the 1 for the first, 2 for the 2nd,etc. My question
where does the Month(Column D) come in. I need to know the 1st, etc. in
Jan. Then I need to know the 1st, etc. in Feb, etc. Sorry I was unclear.
Also can the ranges include blanks?

Postman Jim
 
Back
Top