Matching

[nycard64]

I have two sheets where I am trying to match some info and return a value from a cell in the second sheet. In first sheet FM Data JO02 I am trying to match Process Date and Lot ID to Date and Lot ID in JO02 Total Accum sheet and return the Accum Max value to FM Data JO02 Sheet.

FM Data JO02

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 58px"> <col style="WIDTH: 69px"> <col style="WIDTH: 69px"> <col style="WIDTH: 96px"> <col style="WIDTH: 76px"> <col style="WIDTH: 76px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="HEIGHT: 14px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Proc Eqp Id</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Process Date</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Process Time</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Lot Id</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Dcitem Value</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Total Accum</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2:44 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN303.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">74</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7:07 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN044.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">15</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7:07 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN301.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">54</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7:07 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN483.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">21</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/3/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">8:43 PM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN567.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">9</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/4/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1:24 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN044.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7</td> <td style="FONT-SIZE: 8pt"> </td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

JO02 Total Accum

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 96px"> <col style="WIDTH: 88px"> <col style="WIDTH: 62px"> <col style="WIDTH: 106px"> <col style="WIDTH: 100px"> <col style="WIDTH: 91px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Lot_Id</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Wafer_Id</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Date</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Timestamp</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">TTV Chamber</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Accum Max</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN303.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">46DNX030SJG6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01 02:41:20</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02.DCVD</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">32189</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN303.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">46DNX030SJG6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01 02:41:58</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02.DCVD</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">32228</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN303.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">46DNX030SJG6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01 02:42:37</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02.DCVD</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">32267</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN303.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">46DNX030SJG6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2011-01-01 02:43:15</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02.DCVD</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">32306</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[nycard64]

I converted column B Process Date to date as you laid out, I also made sure that in the other sheet JO02 Total Accum the date column was the same. I typed in the formula and am getting the REF, I am looking through formula to see what is not matching up but can not figure it out. I do have 461 rows in the Total acuum sheet that is the only difference. Thanks so much for your help with this.

FM Data JO02

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 58px"> <col style="WIDTH: 69px"> <col style="WIDTH: 69px"> <col style="WIDTH: 96px"> <col style="WIDTH: 76px"> <col style="WIDTH: 64px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="HEIGHT: 14px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">PLSADDERS_12</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 14px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Proc Eqp Id</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Process Date</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Process Time</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Lot Id</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Dcitem Value</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">Total Accum</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">2:44 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN303.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">74</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7:07 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN044.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">15</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7:07 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN301.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">54</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/1/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7:07 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN483.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">21</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/3/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">8:43 PM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN567.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">9</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/4/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1:24 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN044.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">7</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/4/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1:24 AM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1052MCIN301.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/5/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">9:45 PM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1101MCIN011.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">16</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr> <tr style="HEIGHT: 15px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">JO02</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1/7/2011</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">11:30 PM</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">1101MCIN386.000</td> <td style="TEXT-ALIGN: center; FONT-SIZE: 8pt">111</td> <td style="FONT-SIZE: 8pt">#REF!</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>F4</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D4,IF('JO02 Total Accum '!$C$2:$C$461=B4,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F5</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D5,IF('JO02 Total Accum '!$C$2:$C$461=B5,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F6</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D6,IF('JO02 Total Accum '!$C$2:$C$461=B6,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F7</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D7,IF('JO02 Total Accum '!$C$2:$C$461=B7,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F8</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D8,IF('JO02 Total Accum '!$C$2:$C$461=B8,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F9</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D9,IF('JO02 Total Accum '!$C$2:$C$461=B9,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F10</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D10,IF('JO02 Total Accum '!$C$2:$C$461=B10,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F11</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D11,IF('JO02 Total Accum '!$C$2:$C$461=B11,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr> <tr> <td>F12</td> <td>{=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D12,IF('JO02 Total Accum '!$C$2:$C$461=B12,'JO02 Total Accum '!$F$2:$F$461)))}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Jonmo1]

Check the sheet names in your formulas, they all seem to have a trailing space

=MAX(IF('JO02 Total Accum '!$A$2:$A$461=D4,IF('JO02 Total Accum '!$C$2:$C$461=B4,'JO02 Total Accum '!$F$2:$F$461)))

They should probably be

=MAX(IF('JO02 Total Accum'!$A$2:$A$461=D4,IF('JO02 Total Accum'!$C$2:$C$461=B4,'JO02 Total Accum'!$F$2:$F$461)))

 

[nycard64]

Thank you so much, knew it was something easy but just could not figure it out. All good now, thanks again.