Lookup based on date and name

AlbertanFireFly

New Member
Joined
Jan 23, 2014
Messages
21
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
Here are two ways:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;;">Date Student Punchcard</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;color: #333333;;">10-Jan</td><td style=";">Mary</td><td style=";">4/10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;color: #333333;;">10-Jan</td><td style=";">Kim</td><td style=";">2/5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;color: #333333;;">10-Jan</td><td style=";">Colleen</td><td style=";">7/10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;color: #333333;;">15-Jan</td><td style=";">Mary</td><td style=";">5/10</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;color: #333333;;">15-Jan</td><td style=";">Kim</td><td style=";">3/5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;color: #333333;;">20-Jan</td><td style=";">Mary</td><td style=";">6/10</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;color: #333333;;">20-Jan</td><td style=";">Colleen</td><td style=";">8/10</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="color: #333333;;">Student Punchcard</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="color: #333333;;">Kim</td><td style="color: #333333;;">3/5</td><td style="color: #333333;;">3/5</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="color: #333333;;">Mary</td><td style="color: #333333;;">6/10</td><td style="color: #333333;;">6/10</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="color: #333333;;">Colleen</td><td style="color: #333333;;">8/10</td><td style="color: #333333;;">8/10</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C14</th><td style="text-align:left">=INDEX(<font color="Blue">$C$1:$C$8,SUMPRODUCT(<font color="Red">MAX(<font color="Green">(<font color="Purple">$B$1:$B$8=A14</font>)*ROW(<font color="Purple">$B$1:$B$8</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B14</th><td style="text-align:left">{=INDEX(<font color="Blue">$C$2:$C$8,MATCH(<font color="Red">1,(<font color="Green">$B$2:$B$8=A14</font>)*(<font color="Green">$A$2:$A$8=MAX(<font color="Purple">IF(<font color="Teal">$B$2:$B$8=A14,$A$2:$A$8</font>)</font>)</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

AlbertanFireFly

New Member
Joined
Jan 23, 2014
Messages
21
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,720
Messages
5,626,477
Members
416,187
Latest member
L_D18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top