In this data set, I want to get the most recent or three most recent numbers for a particular day. How can I get this?

Question

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.

Answer 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.