|First, another posting tip:|
When you use the pre tags to post data, please do not include the text of your post within the tags.
As you can see from your post, you end up with long lines of text that make the post hard to read.
In the future, please only include the example data within the pre tags.
One solution to your task is to use a "helper column" and the VLOOKUP function.
1 - On Sheet1, insert a new Column A.
2 - In A2 enter this formula, which should return Smith40914. The goal is to combine the Name and the Date in one cell for use by the VLOOKUP function later.
3 - Drag this down to A7.
4 - Hide Column A if you wish.
5 - In Sheet2, enter this formula in C2, which should return 1, the value at the intersection of Smith and Jan 6, 2012.
This works by combining the name in B2 with the date in C1 and then using VLOOKUP to return the value from Column 6 of the Sheet1!$A$2:$F$7 array.
6 - If you drag the formula down to B7 and then over to G7, you should get the values you are looking for along with a bunch of #N/A errors wherever VLOOKUP couldn't find a match in Column A.
7 - To eliminate the #N/A errors, use an IF(ISNA... function as follows:
Put this in Sheet2!C2, then drag it down and across to G7:
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.