Remember Me?

#1
August 28th 07, 01:32 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 89
INDEX & MATCH functions

I have date info in cells A1:A20 and scores(golf scores) in cells B1:B20. The
same score may appear more than once in cells B1:B20. I am trying to find the
dates on which the lowest scores occur. I can find the first occurence of the
low score by using this formula in cell c1:

=INDEX(A1:A20,MATCH(MIN(B1:B20),B1:B20,-1)*0)

What would I put in cell C2 to find the next occurence (date) of the low
score?

Thank you

#2
August 28th 07, 01:59 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,441
INDEX & MATCH functions

Iriemon,

Insert a new row 1 and enter headings into cells A1 and B1. Then in Cell C2, array enter the
formula (enter using Ctrl-Shift-Enter)

=IF(COUNTIF(\$B:\$B,MIN(B:B))=ROW()-ROW(\$A\$1),INDEX(A:A,
LARGE((\$B\$1:\$B\$1000=MIN(B:B))*ROW(\$B\$1:\$B\$1000),CO UNTIF(\$B:\$B,MIN(B:B))-ROW()+ROW(\$A\$2))),"")

Excel will put {}s around the formula if you enter it correctly. Then copy down for as many rows as
you have values.

HTH,
Bernie
MS Excel MVP

"Iriemon" wrote in message
...
I have date info in cells A1:A20 and scores(golf scores) in cells B1:B20. The
same score may appear more than once in cells B1:B20. I am trying to find the
dates on which the lowest scores occur. I can find the first occurence of the
low score by using this formula in cell c1:

=INDEX(A1:A20,MATCH(MIN(B1:B20),B1:B20,-1)*0)

What would I put in cell C2 to find the next occurence (date) of the low
score?

Thank you

#3
August 28th 07, 03:50 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 89
INDEX & MATCH functions

Worked PERFECTLY!

Thanks

"Bernie Deitrick" wrote:

Iriemon,

Insert a new row 1 and enter headings into cells A1 and B1. Then in Cell C2, array enter the
formula (enter using Ctrl-Shift-Enter)

=IF(COUNTIF(\$B:\$B,MIN(B:B))=ROW()-ROW(\$A\$1),INDEX(A:A,
LARGE((\$B\$1:\$B\$1000=MIN(B:B))*ROW(\$B\$1:\$B\$1000),CO UNTIF(\$B:\$B,MIN(B:B))-ROW()+ROW(\$A\$2))),"")

Excel will put {}s around the formula if you enter it correctly. Then copy down for as many rows as
you have values.

HTH,
Bernie
MS Excel MVP

"Iriemon" wrote in message
...
I have date info in cells A1:A20 and scores(golf scores) in cells B1:B20. The
same score may appear more than once in cells B1:B20. I am trying to find the
dates on which the lowest scores occur. I can find the first occurence of the
low score by using this formula in cell c1:

=INDEX(A1:A20,MATCH(MIN(B1:B20),B1:B20,-1)*0)

What would I put in cell C2 to find the next occurence (date) of the low
score?

Thank you

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post garyr Excel Discussion (Misc queries) 0 February 23rd 07 12:28 AM Damien Excel Worksheet Functions 4 July 29th 06 10:12 PM Malone Excel Worksheet Functions 7 November 16th 05 10:50 PM baz Excel Discussion (Misc queries) 0 September 2nd 05 02:42 PM Maclanders Excel Worksheet Functions 7 February 10th 05 08:31 PM

All times are GMT +1. The time now is 03:17 PM.