On 2002-05-20 12:28, jagsdj wrote:
aladin,
I tried your formulae and it keeps giving me "not found" for all cells. I noticed that ALL references point to Sheet2, shouldn't at least one of the references point to sheet1?
I've tried many variations of the formulae and nothing seems to work. I would REALLY appreciate your help...thanks again.
here is some sample data again:
this is sheet1, SNO = street number
SNO Street City
2097 SUGAR PIKE RD *woodstock*
5450 SUGAR PIKE RD *canton*
sheet2:
Address High Low City
SUGAR PIKE RD 2350 2001 WOODSTOCK
SUGAR PIKE RD 5935 2351 CANTON
the astericks represent what SHOULD be poupulated from sheet2...
thanks again!!
all help is greatly appreciated
Here is Sheet2 where your data is:
<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0><TR><TD COLSPAN=5 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT><FONT COLOR=WHITE>
Microsoft Excel - aaRetVal jagsdj.xls</FONT></TD><TD ALIGN=RIGHT><FONT COLOR=WHITE SIZE=2>___Running: xl2000 : OS = Windows (32-bit) NT 5.00</FONT></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=5><TABLE BORDER=0><TR><TD COLSPAN=5% ALIGN=CENTER BGCOLOR=White>A1</TD><TD COLSPAN=10% ALIGN=RIGHT BGCOLOR=#D4D0C8 >
=</TD><TD COLSPAN=85% ALIGN=LEFT BGCOLOR=White>Address</TD></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>D</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>1</CENTER></TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>
Address</FONT></TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>
High</FONT></TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>
Low</FONT></TD><TD BGCOLOR=#969696 ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>
City</FONT></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>SUGAR PIKE RD</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2350</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2001</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>WOODSTOCK</FONT></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>SUGAR PIKE RD</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5935</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2351</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>CANTON</FONT></TD></TR><TR><TD COLSPAN=5><U>Sheet2</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT>
<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker V1.26]</FONT>
<FONT COLOR=#339966 SIZE=1>If you want FREE SOFT, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo</FONT>
</CENTER>
Here is Sheet1 where you want to retrieve the city value for a given SNO and Street:
<CENTER><TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0><TR><TD COLSPAN=5 BGCOLOR=#0C266B ><TABLE ALIGN=CENTER BORDER=0 WIDTH=100%><TR><TD ALIGN=LEFT><FONT COLOR=WHITE>
Microsoft Excel - aaRetVal jagsdj.xls</FONT></TD><TD ALIGN=RIGHT><FONT COLOR=WHITE SIZE=2>___Running: xl2000 : OS = Windows (32-bit) NT 5.00</FONT></TD></TR></TABLE></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp</TD></TR><TR><TD BGCOLOR=WHITE COLSPAN=5><TABLE BORDER=0><TR><TD COLSPAN=5% ALIGN=CENTER BGCOLOR=White>A1</TD><TD COLSPAN=10% ALIGN=RIGHT BGCOLOR=#D4D0C8 >
=</TD><TD COLSPAN=85% ALIGN=LEFT BGCOLOR=White>SNO</TD></TR></TABLE></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>A</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>B</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>C</CENTER></TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>D</CENTER></TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>1</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>
SNO</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>
Street</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>
City</FONT></TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>2</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2097</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>SUGAR PIKE RD</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(COUNTIF(Sheet2!A:A,B2),INDEX(Sheet2!D:D,SUMPRODUCT((B2=Sheet2!$A$2:$A$3)*(A2%3E=Sheet2!$B$2:$B$3)*(A2%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')><FONT FACE=Arial COLOR=#000000>WOODSTOCK</FONT></A></TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>3</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>5450</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>SUGAR PIKE RD</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(COUNTIF(Sheet2!A:A,B3),INDEX(Sheet2!D:D,SUMPRODUCT((B3=Sheet2!$A$2:$A$3)*(A3%3E=Sheet2!$B$2:$B$3)*(A3%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')><FONT FACE=Arial COLOR=#000000>CANTON</FONT></A></TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER>
<CENTER>4</CENTER></TD><TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>2000</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>KING'S RD</FONT></TD><TD BGCOLOR=#FFCC00 ALIGN=Left VALIGN=BOTTOM ><A HREF=javascript:alert('=IF(COUNTIF(Sheet2!A:A,B4),INDEX(Sheet2!D:D,SUMPRODUCT((B4=Sheet2!$A$2:$A$3)*(A4%3E=Sheet2!$B$2:$B$3)*(A4%3C=Sheet2!$C$2:$C$3)*(ROW(Sheet2!$D$2:$D$3)))),%22Not%20found%22)')><FONT FACE=Arial COLOR=#000000>Not found</FONT></A></TD><TD BGCOLOR=#FFFFFF>
</TD></TR><TR><TD COLSPAN=5><U>Sheet1</U></TD></TR></TABLE>
<FONT COLOR=#339966>To see the formula in the cells just click on the cells hyperlink</FONT>
<FONT COLOR=#339966 SIZE=1>The above image was automatically generated by [HtmlMaker V1.26]</FONT>
<FONT COLOR=#339966 SIZE=1>If you want FREE SOFT, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you</FONT>
<FONT COLOR=#339966 SIZE=1>This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo</FONT>
</CENTER>
Is this not what you wanted to have?
Aladin