0

This is the data set I have :

Date        Day Number
8/29/2016   Mon 63
8/30/2016   Tue 58
8/31/2016   Wed 66
9/1/2016    Thu 61
9/2/2016    Fri 61
9/3/2016    Sat 67
9/4/2016    Sun 70
9/5/2016    Mon 50
9/6/2016    Tue 51
9/7/2016    Wed 59
9/8/2016    Thu 60
9/9/2016    Fri 62
9/10/2016   Sat 56
9/11/2016   Sun 55
9/12/2016   Mon 52
9/13/2016   Tue 53
9/14/2016   Wed 69
9/15/2016   Thu 51
9/16/2016   Fri 58
9/17/2016   Sat 64
9/18/2016   Sun 50
9/19/2016   Mon 68
9/20/2016   Tue 53
9/21/2016   Wed 57
9/22/2016   Thu 61
9/23/2016   Fri 58
9/24/2016   Sat 56
9/25/2016   Sun 61

This is the output I am expecting :

For example, what are the most recent Sunday values ? :

Sun 61
     50
     55

One idea I had was using the 'Large' function to get the highest three numbers in the date column.

However, I cannot do this for specific days. I couldn't think of other ways to combine the 'if' and the 'Large" function.

1

Use this array formula:

=INDEX($C$2:$C$29,MATCH(LARGE(IF($B$2:$B$29=$F$8,$A$2:$A$29),ROW(1:1)),$A$2:$A$29,0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter upon exiting edit mode instead of Enter. If done correctly then Excel will put {} around the formula.

So I put this in G8, pressed Ctrl-Shift-Enter then copied down.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.