vlookup where lookup value appears more than once

[Baltazar]

Hi, I’ve been trying this out for a while but am having no luck.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’m a geologist and am trying to create an automated monthly reporting system for drilling information. I have simplified the tables below for ease of explanation. A database might be an easier option but I want to use excel in this instance.<o></o>
<o></o>
A B C D<o></o>

<TABLE class=MsoNormalTable style="MARGIN: auto auto auto -1.15pt; WIDTH: 251pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=335 border=0><TBODY><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; mso-border-alt: solid windowtext 1.0pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=76>

Month Code<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=103>

Date<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=64>

Hole ID<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt" vAlign=bottom noWrap width=92>

No of Samples<o></o>




​

</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

1<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

02-Jan-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH1<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

50<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

1<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

07-Jan-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH2<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

45<o></o>




​

</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

2<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

01-Feb-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH3<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

35<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

2<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

05-Feb-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH4<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

69<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

2<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

06-Feb-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH5<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

23<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 6"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

3<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

08-Mar-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH6<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

89<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 7"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

3<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

09-Mar-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH7<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

54<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 8"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

4<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

11-Apr-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH8<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

15<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 9"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

4<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

12-Apr-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH9<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

25<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 10"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

4<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

13-Apr-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH10<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

28<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 11"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

4<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

15-Apr-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH11<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

89<o></o>




​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 12"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

4<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=103>

16-Apr-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DH12<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=92>

63<o></o>




​

</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 13; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 57pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext 1.0pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=76>

4<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 77pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=103>

18-Apr-09<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=64>

DH13<o></o>




​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 69pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=92>

87<o></o>​

​

</TD></TR></TBODY></TABLE>​

<o></o>
The table above is the input table (A1:D14). I have created column A to assist the vlookup. It will act as the reference for the lookup value [=MONTH(B*)].
<o></o>
<o></o>
H I J<o></o>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto -1.15pt; WIDTH: 201pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt" cellSpacing=0 cellPadding=0 width=268 border=0><TBODY><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=107>Monthly Report:<o></o>


</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 121pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=161 colSpan=2>January 2009<o></o>


</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; mso-border-alt: solid windowtext 1.0pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>Date<o></o>


</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 41.5pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=55>Hole ID<o></o>


</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: white; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 79.5pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt" vAlign=bottom noWrap width=106>No of Samples<o></o>

​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=107>02-Jan-09<o></o>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 41.5pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=55>DH1<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 79.5pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext 1.0pt; mso-border-bottom-alt: solid windowtext .5pt" vAlign=bottom noWrap width=106>50<o></o>

​

</TD></TR><TR style="HEIGHT: 15.75pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext 1.0pt; mso-border-left-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=107>07-Jan-09<o></o>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 41.5pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-border-right-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext 1.0pt" vAlign=bottom noWrap width=55>DH2<o></o>

​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 79.5pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" vAlign=bottom noWrap width=106>45<o></o>



</TD></TR></TBODY></TABLE>
<o></o>
This table will act as the automatically generated monthly report (H1:J3). “January 2009” is the only input required. It displays the date drilled, the hole id and the number of samples taken.
<o></o>
<o></o>
I have figured out that using vlookup, I can get the Date field populated using:<o></o>
=IFERROR(IF($I$1="January 2009",VLOOKUP(1,$A2:$D$143,2,FALSE),""),"")
<o></o>
<o></o>
The Hole_ID by:<o></o>
=IFERROR(IF($I$1="January 2009",VLOOKUP(1,$A2:$D$143,3,FALSE),""),"")
<o></o>
<o></o>
And the No. of Samples by:<o></o>
=IFERROR(IF($I$1="January 2009",VLOOKUP(1,$A2:$D$143,4,FALSE),""),"")
<o></o>
<o></o>
The vlookup function falls apart when I try to generated a report for February etc. This is because there is more than occurrence of the lookup value. I have cheated the January Report by playing around with the absolute references but this is not ideal. I want the report to be streamlined and requiring very little manipulating.
<o></o>
<o></o>
Please help…..
<o></o>
<o></o>

 

[Aladin Akyurek]

What is desired: Report separately multiple occurrences or once with sample figures summed?

 

[Baltazar]

I looking for a solution......

How do you handle a lookup table when the look-up value appears more than once? In the case I provided, the look-up value (column A) has two occurrence of "1", three of "2", two of "3" and 6 occurrences of "4".
A vlookup works fine when the look-up values are individual but in my case, the lookup table only takes the first instance of the value and disregards the rest.
Is there a way around this in vlookup or by using another function?

Any help would be greatly appreciated.....

Cheers

 

[Baltazar]

I looking for a solution......report separately showing all the occurences in a given month.

How do you handle a lookup table when the look-up value appears more than once? In the case I provided, the look-up value (column A) has two occurrence of "1", three of "2", two of "3" and 6 occurrences of "4".
A vlookup works fine when the look-up values are individual but in my case, the lookup table only takes the first instance of the value and disregards the rest.
Is there a way around this in vlookup or by using another function?

Any help would be greatly appreciated.....

Cheers

 

[Aladin Akyurek]

H:J...

<TABLE style="WIDTH: 193pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=257 x:str><COLGROUP><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 75pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2173960 class=xl22 height=17 width=100> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=93> </TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Monthly Report:

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 align=right x:num="39814">1-Jan-09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22> </TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Hole ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>No of Samples</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39815">2-Jan-09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>DH1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>50</TD></TR>

<TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 align=right x:num="39820">7-Jan-09</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23>DH2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num>45</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 5" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 x:str=""> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 x:str=""> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 6" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 x:str=""> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 x:str=""> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str=""> </TD></TR></TBODY></TABLE>

I1:

Control+shift+enter, just enter...

=SUM(IF(B2:B14-DAY(B2:B14)+1=I2,1))

I2: 1-Jan-09

which is a first day date of a month/year of interest.

H4:

Control+shift+enter, not just enter...

=IF(ROWS(H$3:H3)<=$I$1,INDEX(B$2:B$14,
   SMALL(IF($B$2:$B$14-DAY($B$2:$B$14)+1=$I$2,
    ROW($B$2:$B$14)-ROW($B$2)+1),ROWS(H$3:H3))),"")

and copy across then down.

Format column J as General.

 

[talent_anand]

Period/ Class I II III XII (Com./Sci.) Eco./Maths/Bio. Shilpa/Gurpreet/Govind Physical Edu. / Science Nishant Physics / Accounts Naveen / Suman B. XI (Com./Sci.) Physics / Accountancy Naveen / Suman B. Eco./ Maths/ Biology Shilpa, Gurpreet, Govind Phy. Edu. (Science & Commerce) Nishant XII (Arts) Physical Education - Nishant Eng. (1-6) - Shailesh (Arts & Comm.) Geography (1-6) Hari Singh XI (Arts) Physical Education - Nishant Sociology - Suman B. Geography (1-6) Hari Singh X - A Hindi - Hari Singh Maths - Kartar Science (Bio.) 1-3 Chemistry (4-6) Govind X - B English - Shailesh Science - Naveen Maths - Gurpreet X - C Hindi - Sunita S.St. - Monika English - Shailesh IX - A Maths - Kartar Hindi - Sunita S.St. - Monika IX - B Science - Ajay English - Anil Maths - Kartar IX - C English - Anil Science - Ajay Hindi - Sunita VIII - A S.St. - Kailash Maths - Sujata Comp. (1-2) Anand G.P. (3-4) Parveen P. G.K. (5-6) Seema <colgroup><col style="mso-width-source:userset;mso-width-alt:4644;width:95pt" width="127"> <col style="mso-width-source:userset;mso-width-alt:15433;width:317pt" width="422"> <col style="mso-width-source:userset;mso-width-alt:9472; width:194pt" span="2" width="259"> </colgroup><tbody> </tbody>

<tbody>
</tbody>

I have such a table structure in excel for time table . I want to get teacher wise time table.
If I fill any name of teacher in any cell then in the preceeding cell it must return his time table i.e. which period and in which class.
Say I write name Seema in cell A13. It gives me result. But if any teacher has same period in two classes. Say (1-3) Monday - Wednesday 4th period in 7th class. and same period in 8th class (4 - 6) thursday to saturday.
The formula gives me only first value.

b13 c13

1	=VLOOKUP(CONCATENATE("*", A13,"*"),$B$50:$I$80,1,FALSE)
2	=VLOOKUP(CONCATENATE("*", A13,"*"),$C$50:$I$80,1,FALSE)
3	=VLOOKUP(CONCATENATE("*", A13,"*"),$D$50:$I$80,1,FALSE)
4	=VLOOKUP(CONCATENATE("*", A13,"*"),$E$50:$I$80,1,FALSE)
5	=VLOOKUP(CONCATENATE("*", A13,"*"),$F$50:$I$80,1,FALSE)
6	=VLOOKUP(CONCATENATE("*", A13,"*"),$G$50:$I$80,1,FALSE)

<colgroup><col><col></colgroup><tbody>
</tbody>

Please help