Quick Help Please!!

[Alex O]

I need a formula for AP2 that will look for the value in M3 in AN3:AN6806 and if there'a a match AND the status codes are the same return the status code in AO. If they are not the same return the value in N. In short, the data set on the left is new the data on the right is old. I need to know which status codes have changed...

Thanks

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 86px"><COL style="WIDTH: 86px"><COL style="WIDTH: 86px"><COL style="WIDTH: 79px"><COL style="WIDTH: 60px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>M</TD><TD>N</TD><TD>AN</TD><TD>AO</TD><TD>AP</TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="BACKGROUND-COLOR: #c0c0c0"> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BACKGROUND-COLOR: #c0c0c0">DEBT_ID_NO</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Status Code</TD><TD style="BACKGROUND-COLOR: #c0c0c0">DEBT_ID_NO</TD><TD style="BACKGROUND-COLOR: #c0c0c0">Status Code</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274541</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">503</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274541</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">503</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274542</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">117</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274542</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">117</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274543</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274543</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274544</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274544</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274545</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">520</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274545</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">520</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274546</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">405</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274546</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">405</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274547</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274547</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274548</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274548</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">458</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274549</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">420</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">274549</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">420</TD><TD> </TD></TR></TBODY></TABLE>

 

[njimack]

=if(sumproduct(--(an3:an6806=m3),--(ao3:ao6806=n3)),ao3,n3)

 

[Alex O]

Thanks for the quick response! We're going in the right direction - but, I need a way to scan the entire range for a match because the two data sets are not the same (ie new accounts have been added ince last week).


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 86px"><COL style="WIDTH: 86px"><COL style="WIDTH: 86px"><COL style="WIDTH: 79px"><COL style="WIDTH: 60px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>L</TD><TD>M</TD><TD>AM</TD><TD>AN</TD><TD>AO</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5789</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319699</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">168</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319699</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">168</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">168</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5790</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319700</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">405</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319700</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">405</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">405</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5791</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319701</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">131</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319701</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">131</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">131</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5792</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00">319703</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00">502</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00">319702</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffcc00">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">502</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5793</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319704</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">520</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319703</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">502</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">520</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5794</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319705</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319704</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">520</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">450</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5795</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319706</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319705</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">450</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5796</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319707</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319706</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">450</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5797</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319708</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">502</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319707</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">502</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5798</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319709</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">520</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319708</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">502</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">520</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5799</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319710</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">450</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">319709</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0">520</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99ccff">450</TD></TR></TBODY></TABLE>

 

[njimack]

I don't understand - you need to explain the expected result given your sample of data

 

[Alex O]

Sorry....I was misreading how this was performing. Is there an easy way to identify the staus codes that changed?