Lookup based on date and name

[AlbertanFireFly]

Hi,

I'm a fitness instructor, and I have an excel file to keep track of my classes, the date and time, the student's name, and whether they paid cash or used a punchcard. Right now, my master sheet looks like:

Date Student Punchcard
10-Jan Mary 4/10
10-Jan Kim 2/5
10-Jan Colleen 7/10
15-Jan Mary 5/10
15-Jan Kim 3/5
20-Jan Mary 6/10
20-Jan Colleen 8/10


What I would like to be able to do, is have a second table that will automatically lookup the most recent time that each student attended a class, and then tell me how many punches they have left on their card. So, I would want the new table to show

Student Punchcard
Kim 3/5
Mary 6/10
Colleen 8/10

How am I able to look up each individual student, find the most recent date that they attended, and then have it spit out their punchcard value?

Thanks so much,
- AlbertanFireFly

 

[Comfy]

Here are two ways:

Excel 2010
	A	B	C
1	Date Student Punchcard
2	10-Jan	Mary	4/10
3	10-Jan	Kim	2/5
4	10-Jan	Colleen	7/10
5	15-Jan	Mary	5/10
6	15-Jan	Kim	3/5
7	20-Jan	Mary	6/10
8	20-Jan	Colleen	8/10
9
10
11
12
13	Student Punchcard
14	Kim	3/5	3/5
15	Mary	6/10	6/10
16	Colleen	8/10	8/10
Sheet1

Cell Formulas
Range	Formula
C14	=INDEX($C$1:$C$8,SUMPRODUCT(MAX(($B$1:$B$8=A14)*ROW($B$1:$B$8))))
B14	{=INDEX($C$2:$C$8,MATCH(1,($B$2:$B$8=A14)*($A$2:$A$8=MAX(IF($B$2:$B$8=A14,$A$2:$A$8))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AlbertanFireFly]

Ok, that worked with the small 8 row example, but when I applied it to my 500 row sheet, it didn't. The array formula just gave me back 0s the whole way down the list. And the worksheet formula actually gave back answers like 2/5 or 8/10, but when I double checked them, they were all wrong. I tried it on my master list, which is set up as a table, then tried it without the table formatting, tried it from a separate sheet, then the same one. Always the 0s...

 

[AlbertanFireFly]

Haha! I've got it figured! Thanks so much!