vlookup where lookup value appears more than once

B

Baltazar

New Member
Joined
Jan 9, 2010
Messages
3
Hi, I’ve been trying this out for a while but am having no luck.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
A B C D<o:p></o:p>
<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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>




​
</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:p></o:p>​



​
</TD></TR></TBODY></TABLE>​
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
H I J<o:p></o:p>
<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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>

​
</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:p></o:p>



</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:p></o:p>

​
</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:p></o:p>


​
</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:p></o:p>



</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:p></o:p>

​
</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:p></o:p>



</TD></TR></TBODY></TABLE>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
I have figured out that using vlookup, I can get the Date field populated using:<o:p></o:p>
=IFERROR(IF($I$1="January 2009",VLOOKUP(1,$A2:$D$143,2,FALSE),""),"")
<o:p></o:p>
<o:p></o:p>
The Hole_ID by:<o:p></o:p>
=IFERROR(IF($I$1="January 2009",VLOOKUP(1,$A2:$D$143,3,FALSE),""),"")
<o:p></o:p>
<o:p></o:p>
And the No. of Samples by:<o:p></o:p>
=IFERROR(IF($I$1="January 2009",VLOOKUP(1,$A2:$D$143,4,FALSE),""),"")
<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Please help…..
<o:p></o:p>
<o:p></o:p>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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...
Code: 
=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.
 
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

<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>
[TD="class: xl67, width: 127"]Period/ Class[/TD]
[TD="class: xl67, width: 422"]I[/TD]
[TD="class: xl67, width: 259"]II[/TD]
[TD="class: xl67, width: 259"]III[/TD]

[TD="class: xl66, width: 127"]XII
(Com./Sci.)[/TD]
[TD="class: xl66, width: 422"]Eco./Maths/Bio.
Shilpa/Gurpreet/Govind[/TD]
[TD="class: xl66, width: 259"]Physical Edu. / Science
Nishant[/TD]
[TD="class: xl66, width: 259"]Physics / Accounts
Naveen / Suman B.[/TD]

[TD="class: xl66, width: 127"]XI
(Com./Sci.)[/TD]
[TD="class: xl66, width: 422"]Physics / Accountancy
Naveen / Suman B.[/TD]
[TD="class: xl66, width: 259"]Eco./ Maths/ Biology
Shilpa, Gurpreet, Govind[/TD]
[TD="class: xl66, width: 259"]Phy. Edu. (Science & Commerce)
Nishant[/TD]

[TD="class: xl66, width: 127"]XII
(Arts)[/TD]
[TD="class: xl66, width: 422"]Physical Education -
Nishant[/TD]
[TD="class: xl66, width: 259"]Eng. (1-6) - Shailesh
(Arts & Comm.)[/TD]
[TD="class: xl66, width: 259"]Geography (1-6)
Hari Singh[/TD]

[TD="class: xl66, width: 127"]XI
(Arts)[/TD]
[TD="class: xl66, width: 422"]Physical Education -
Nishant[/TD]
[TD="class: xl66, width: 259"]Sociology - Suman B.[/TD]
[TD="class: xl66, width: 259"]Geography (1-6)
Hari Singh[/TD]

[TD="class: xl66, width: 127"]X - A[/TD]
[TD="class: xl66, width: 422"]Hindi - Hari Singh[/TD]
[TD="class: xl66, width: 259"]Maths - Kartar [/TD]
[TD="class: xl66, width: 259"]Science (Bio.) 1-3
Chemistry (4-6) Govind[/TD]

[TD="class: xl66, width: 127"]X - B[/TD]
[TD="class: xl66, width: 422"]English - Shailesh[/TD]
[TD="class: xl66, width: 259"]Science - Naveen[/TD]
[TD="class: xl66, width: 259"]Maths - Gurpreet[/TD]

[TD="class: xl66, width: 127"]X - C[/TD]
[TD="class: xl66, width: 422"]Hindi - Sunita[/TD]
[TD="class: xl66, width: 259"]S.St. - Monika[/TD]
[TD="class: xl66, width: 259"]English - Shailesh[/TD]

[TD="class: xl66, width: 127"]IX - A[/TD]
[TD="class: xl66, width: 422"]Maths - Kartar[/TD]
[TD="class: xl66, width: 259"]Hindi - Sunita[/TD]
[TD="class: xl66, width: 259"]S.St. - Monika[/TD]

[TD="class: xl66, width: 127"]IX - B[/TD]
[TD="class: xl66, width: 422"]Science - Ajay[/TD]
[TD="class: xl66, width: 259"]English - Anil[/TD]
[TD="class: xl66, width: 259"]Maths - Kartar[/TD]

[TD="class: xl66, width: 127"]IX - C[/TD]
[TD="class: xl66, width: 422"]English - Anil[/TD]
[TD="class: xl66, width: 259"]Science - Ajay[/TD]
[TD="class: xl66, width: 259"]Hindi - Sunita[/TD]

[TD="class: xl66, width: 127"]VIII - A[/TD]
[TD="class: xl66, width: 422"]S.St. - Kailash[/TD]
[TD="class: xl66, width: 259"]Maths - Sujata[/TD]
[TD="class: xl66, width: 259"]Comp. (1-2) Anand
G.P. (3-4) Parveen P.
G.K. (5-6) Seema[/TD]

</tbody>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 296"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]=VLOOKUP(CONCATENATE("*", A13,"*"),$B$50:$I$80,1,FALSE)
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]=VLOOKUP(CONCATENATE("*", A13,"*"),$C$50:$I$80,1,FALSE)
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]=VLOOKUP(CONCATENATE("*", A13,"*"),$D$50:$I$80,1,FALSE)
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=VLOOKUP(CONCATENATE("*", A13,"*"),$E$50:$I$80,1,FALSE)
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=VLOOKUP(CONCATENATE("*", A13,"*"),$F$50:$I$80,1,FALSE)
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]=VLOOKUP(CONCATENATE("*", A13,"*"),$G$50:$I$80,1,FALSE)
[/TD]
[/TR]
</tbody>[/TABLE]
Please help
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,223,276
Messages
6,171,140
Members
452,381
Latest member
Nova88

Share this page

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
Back
Top