INDEX & MATCH functions

I

Iriemon

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
 
B

Bernie Deitrick

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),COUNTIF($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
 
I

Iriemon

Worked PERFECTLY!

Thanks



Bernie Deitrick said:
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),COUNTIF($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
 

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