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 |
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 |
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 |
All times are GMT +1. The time now is 06:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com