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

 

[Jonmo1]

On the 2nd sheet, There are 2 listings for
Lot ID 1052MCIN303.000 Date 2011-01-01

Which one would you want returned?

 

[CalvinGraham]

You can do this with fancy sumproduct() funcitons but that could slow things down if it's a big spreadsheet. Search for 'multiple lookup' or the like to get an idea.

Personally, I'd add a hidden pair of columns like $G4 = $B4&$D4 to create single cell values, then you can do a straight lookup on them.

 

[nycard64]

The first Accum value would work for what I am trying to do.

 

[Jonmo1]

Or considering that the lable you have in the first sheet F3 is "Total Accum"
Does this mean you would want to ADD the two together?

Given example I pointed out, the 2 Accum Values are
32189 and 32228

would you want 32189 or 64417 (32189+32228)
?

 

[T. Valko]

The first Accum value would work for what I am trying to do.

Try this array formula**:

=INDEX('JO02 Total Accum'!F$2:F$5,MATCH(1,IF('JO02 Total Accum'!A$2:A$5=D4,IF('JO02 Total Accum'!C$2:C$5=B4,1)),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Jonmo1]

Hmm, upon further looking, it looks like ALL 4 on the 2nd sheet match the lot_id and date...

So it seems you do want to SUM them...

Try this in the first sheet, F4

=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$5=D4),--('JO02 Total Accum'!$C$2:$C$5=B4),'JO02 Total Accum'!$F$2:$F$5)

 

[nycard64]

I do need to sum all the values if there are more them one. Can we just pull the largest value if there is more then the one associated with the same date and lot id? I put the formula in and I get all Zeros

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="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</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="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</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="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</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="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</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="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</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="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</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="TEXT-ALIGN: center; FONT-SIZE: 8pt">0</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>=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$461=D4),--('JO02 Total Accum'!$C$2:$C$461=B4),'JO02 Total Accum'!$F$2:$F$461)</td></tr> <tr> <td>F5</td> <td>=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$5=D5),--('JO02 Total Accum'!$C$2:$C$5=B5),'JO02 Total Accum'!$F$2:$F$5)</td></tr> <tr> <td>F6</td> <td>=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$5=D6),--('JO02 Total Accum'!$C$2:$C$5=B6),'JO02 Total Accum'!$F$2:$F$5)</td></tr> <tr> <td>F7</td> <td>=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$5=D7),--('JO02 Total Accum'!$C$2:$C$5=B7),'JO02 Total Accum'!$F$2:$F$5)</td></tr> <tr> <td>F8</td> <td>=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$5=D8),--('JO02 Total Accum'!$C$2:$C$5=B8),'JO02 Total Accum'!$F$2:$F$5)</td></tr> <tr> <td>F9</td> <td>=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$5=D9),--('JO02 Total Accum'!$C$2:$C$5=B9),'JO02 Total Accum'!$F$2:$F$5)</td></tr> <tr> <td>F10</td> <td>=SUMPRODUCT(--('JO02 Total Accum'!$A$2:$A$5=D10),--('JO02 Total Accum'!$C$2:$C$5=B10),'JO02 Total Accum'!$F$2:$F$5)</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[nycard64]

Sorry typo I DO NOT need to sum the accum values just need to largest one. Sorry for confusion.

 

[Jonmo1]

First problem,

It appears that the dates in column B of the first sheet (FM Data JO02) are not really dates, just text strings that look like dates.

You need to convert them.
Try using Text to columns

Highlight column B
Click Data - Text To Columns
Select Deliminated
Click Next - Next
Under Column data format, choose Date - MDY
Click Finish.


Then try this array formula in F4 on the first sheet

=MAX(IF('JO02 Total Accum '!$A$2:$A$5=D2,IF('JO02 Total Accum '!$C$2:$C$5=B2,'JO02 Total Accum '!$F$2:$F$5)))

Array formula needs to be entered with CTRL + SHIFT + ENTER