Database question clarification
 

I guess I need a little more guidance. I submitted the following question
earlier:


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?

__________________________________________________ __________________
Then I received this answer:

One way (array-entered):

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*$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 say the month of
Jan
(column D)

Thanks again for any help

Database question clarification
 

Without testing the original formula, if it worked then:

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*($D$2:$D$1000="Jan")*
$E$2:$E$1000,1),($B$2:$B$1000="North")*($D$2:$D$10 00="Jan")*$E$2:$E$1000,0))

should work as well.

--

Vasant





"Jim" wrote in message ...
I guess I need a little more guidance. I submitted the following question
earlier:


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?

__________________________________________________ __________________
Then I received this answer:

One way (array-entered):

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*$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 say the month
of
Jan
(column D)

Thanks again for any help

Database question clarification
 

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

--
Regards,
Tom Ogilvy

"Jim" wrote in message ...
I guess I need a little more guidance. I submitted the following question
earlier:


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?

__________________________________________________ __________________
Then I received this answer:

One way (array-entered):

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*$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 say the month
of
Jan
(column D)

Thanks again for any help

Database question clarification
 

Why don't you just use a pivottable to show the data.
Dennis

"Jim" wrote in message ...
I guess I need a little more guidance. I submitted the following question
earlier:


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?

__________________________________________________ __________________
Then I received this answer:

One way (array-entered):

=INDEX($A$2:$A$1000,MATCH(LARGE(($B$2:$B$1000="Nor th")*$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 say the month
of
Jan
(column D)

Thanks again for any help