Index/Match

M

Mike

Hello All,
Using Excel XP

I want to find the three highest times (COLUMN A and COLUMN D) and have the
corresponding Month/Year (COLUMN B and COLUMN E) match.
For example the 3 highest times and Month/Year would be:

1. 25:24:22 Jan 2004
2. 22:49:48 Jan 2003
3. 16:44:42 Jul 2004

Here's is a formula that I have, but it returns #N/A

=INDEX($B$1:$B$4,$E$1:$E$4,MATCH(LARGE(($A$1:$A$4,$D$1:$D$4),1),($A$1:$A$4,$
D$1:$D$4),0))

A B C D E
1 25:24:22 Jan 2004 16:44:42 Jul 2004
2 22:49:48 Jan 2003 15:37:23 Jul 2003
3 13:00:37 Jan 2002 13:22:08 Jul 2001
4 12:57:39 Jan 2001 11:16:22 Jul 2000


Any help would be appreciated in getting the formula to work correctly.

Thank you,
Michael
 
B

Bernie Deitrick

Michael,

=LARGE(($A$1:$A$4,$C$1:$C$4),1)

Returns the highest value - should be formatted for time.

=IF(ISNA(INDEX(B:B,MATCH(LARGE(($A$1:$A$4,$C$1:$C$4),1),A:A,FALSE),1)),INDEX
(D:D,MATCH(LARGE(($A$1:$A$4,$C$1:$C$4),1),C:C,FALSE),1),INDEX(B:B,MATCH(LARG
E(($A$1:$A$4,$C$1:$C$4),1),A:A,FALSE),1))

Returns the associated date - should be formatted as a date.

HTH,
Bernie
MS Excel MVP
 
M

Mike

Bernie,
Thanks for the quick response. In the formula that you sent it returns the
highest value. I want to continue down so that I want it to find the
highest 3 values. Where do I substitue 2 for the second highest? Would it
be the 1 that follows "FALSE"? I've tried substiuting 2, 3 but it returns a
date of Jan 1900, which is incorrect.
Thanks

Michael
 
B

Bernie Deitrick

Mike,

You need to update the three LARGE functions: Replace each of the 3 XXXXs
with 2, etc..

=IF(ISNA(INDEX(B:B,MATCH(LARGE(($A$1:$A$4,$C$1:$C$4),XXXX),A:A,FALSE),1)),IN
DEX
(D:D,MATCH(LARGE(($A$1:$A$4,$C$1:$C$4),XXXX),C:C,FALSE),1),INDEX(B:B,MATCH(L
ARGE(($A$1:$A$4,$C$1:$C$4),XXXX),A:A,FALSE),1))

HTH,
Bernie
MS Excel MVP
 
M

Mike

Hi Bernie,
Sorry to be a pain.
The formula works when the value is based on column A. In this example
when it returns the 3rd highest value of 16:44:42 it returns a date of Jan
1900, instead of Jul 2004. Any help on this situation?
Thanks

Michael
 
B

Bernie Deitrick

Mike,

=IF(ISNA(INDEX(B:B,MATCH(LARGE(($A$1:$A$4,$C$1:$C$4),3),A:A,FALSE),1)),INDEX
(D:D,MATCH(LARGE(($A$1:$A$4,$C$1:$C$4),3),C:C,FALSE),1),INDEX(B:B,MATCH(LARG
E(($A$1:$A$4,$C$1:$C$4),3),A:A,FALSE),1))

Returns Jul 2004 for me.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top