Criteria search

[ffgilby]

Hello,

I am looking for a way to enter criteria in cell A1 and find the match within that column (A), then also do the exact same, but in column B. Easy enough, but what I need is to search where criteria in column A aligns or is next to the criteria in column B. Each of these columns contain around 48,000 rows, and the criteria entered may be in each column upwards of 50 or more times, but there will only be 1 instance where criteria 1 and 2 will be adjacent, if they are at all. When they do align, column C will have either 0, 1, or 9
So what I am looking for is a way to do this search and match, possibly give the exact row that contains this match, or say yes or no, and then the text from column C
A co-worker and myself have been trying to come with a solution, but to nothing yet.
Any ideas would be greatly appreciated.

 

[ffgilby]

OK, here is what I have made so far. I was able to import the spreadsheet as I wanted (somewhat) to show what I would like to accomplish.

<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; 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>D3</td> <td>{=INDEX(D4:D8710,MATCH(1,IF(B4:B8710=B3,IF(C4:C8710=C3,1)),0))}</td></tr> <tr> <td>E3</td> <td>{=IF(D3="","",MATCH(1,IF(B4:B8710=B3,IF(C4:C8710=C3,1)),0)+ROW(B4)-1)}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>


<table style="width: 424px; height: 435px;" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> A
</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr><td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Column1/Column 2 Edits</td> <td style="BACKGROUND-COLOR: #99cc00">
</td> <td style="BACKGROUND-COLOR: #99cc00">
</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr><tr><td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">Column 1</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">Column 2</td> <td style="TEXT-ALIGN: left">Modifier
0=NO
1=YES
9=N/A</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">ROW #</td></tr><tr><td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 14pt; FONT-WEIGHT: bold">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 14pt; FONT-WEIGHT: bold">12046</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 14pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold">6918</td></tr><tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6918</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12046</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6919</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12047</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6920</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12047</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6921</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12051</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6922</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12051</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6923</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12052</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6924</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12052</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6925</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12053</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6926</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12053</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6927</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12054</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6928</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12054</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6929</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12055</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6930</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12055</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6931</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12056</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6932</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12056</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6933</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12057</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6934</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">12057</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6935</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">13100</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6936</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">13100</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6937</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">13101</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6938</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">13101</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr></tbody></table>

 

[T. Valko]

OK, here is what I have made so far. I was able to import the spreadsheet as I wanted (somewhat) to show what I would like to accomplish.

<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D3</TD><TD>{=INDEX(D4:D8710,MATCH(1,IF(B4:B8710=B3,IF(C4:C8710=C3,1)),0))}</TD></TR><TR><TD>E3</TD><TD>{=IF(D3="","",MATCH(1,IF(B4:B8710=B3,IF(C4:C8710=C3,1)),0)+ROW(B4)-1)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>


<TABLE style="WIDTH: 424px; HEIGHT: 435px" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>A

</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #99cc00">Column1/Column 2 Edits</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">Column 1</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">Column 2</TD><TD style="TEXT-ALIGN: left">Modifier
0=NO
1=YES
9=N/A
</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">ROW #</TD></TR><TR><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12046</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; FONT-FAMILY: Verdana; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">6918</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6918</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12046</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6919</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12047</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6920</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12047</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6921</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12051</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6922</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12051</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6923</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12052</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6924</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12052</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6925</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12053</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6926</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12053</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6927</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12054</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6928</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12054</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6929</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12055</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6930</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12055</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6931</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12056</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6932</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12056</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6933</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12057</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6934</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">12057</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6935</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">13100</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6936</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">13100</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6937</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">13101</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6938</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">11440</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">13101</TD><TD style="BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: left">1</TD><TD style="BACKGROUND-COLOR: #99cc00">

</TD></TR></TBODY></TABLE>

OK, still not clear what you mean by: automatically scroll to the cell.

So, based on those formula results what should happen?

 

[ffgilby]

OK, still not clear what you mean by: automatically scroll to the cell.

So, based on those formula results what should happen?

OK, so the way I have this built now (thanks to your formulas) is criteria is entered in B3 & C3, then if a match is made, the number from column D is displayed in D3, then E3 displays the exact row with the match.
Here is how the match displays now:


<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: 64px"> <col style="WIDTH: 69px"> <col style="WIDTH: 61px"> <col style="WIDTH: 79px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td>
</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="HEIGHT: 19px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="BACKGROUND-COLOR: #99cc00; FONT-SIZE: 12pt; FONT-WEIGHT: bold">Column1/Column 2 Edits</td> <td style="BACKGROUND-COLOR: #99cc00">
</td> <td style="BACKGROUND-COLOR: #99cc00">
</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 84px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">Column 1</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">Column 2</td> <td style="TEXT-ALIGN: left">Modifier
0=NO
1=YES
9=N/A</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00; FONT-WEIGHT: bold">ROW #</td></tr> <tr style="HEIGHT: 34px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 14pt; FONT-WEIGHT: bold">11440</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 14pt; FONT-WEIGHT: bold">12046</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00; FONT-SIZE: 14pt; FONT-WEIGHT: bold">1</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00; FONT-FAMILY: Verdana; FONT-SIZE: 12pt; FONT-WEIGHT: bold">6918</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">C8950</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">C8952</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">G0345</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">G0347</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">G0351</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">G0353</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">G0354</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">J2001</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">0213T</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">0216T</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">19290</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">36000</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">36410</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">37202</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">62318</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">62319</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">64415</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">64416</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">64417</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">64450</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">10021</td> <td style="TEXT-ALIGN: right; BACKGROUND-COLOR: #99cc00">64470</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #99cc00">1</td> <td style="BACKGROUND-COLOR: #99cc00">
</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; 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>D3</td> <td>{=INDEX(D4:D8710,MATCH(1,IF(B4:B8710=B3,IF(C4:C8710=C3,1)),0))}</td></tr> <tr> <td>E3</td> <td>{=IF(D3="","",MATCH(1,IF(B4:B8710=B3,IF(C4:C8710=C3,1)),0)+ROW(B4)-1)}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

What I would like is for the spreadsheet to automatically scroll or go to what ever row is displayed in E3, as the sample I imported into the last posting.
Hope this makes sense, if it's not feasible, just let me know. I have already been able to make a spreadsheet that is very functional and time saving.

Thanks, Brian.

 

[T. Valko]

OK, so the way I have this built now (thanks to your formulas) is criteria is entered in B3 & C3, then if a match is made, the number from column D is displayed in D3, then E3 displays the exact row with the match. What I would like is for the spreadsheet to automatically scroll or go to what ever row is displayed in E3, as the sample I imported into the last posting.
Hope this makes sense, if it's not feasible, just let me know. I have already been able to make a spreadsheet that is very functional and time saving.

Thanks, Brian.

We can do this with a hyperlink. Cell E3 will be a hyperlink formula. It will display the row number and it will be a hyperlink to that row (well, a cell on that row, like cell A????).

Is that something that will work for you?

 

[ffgilby]

We can do this with a hyperlink. Cell E3 will be a hyperlink formula. It will display the row number and it will be a hyperlink to that row (well, a cell on that row, like cell A????).

Is that something that will work for you?

The hyperlink would work great. How would install this? I just tried inserting a hyperlink, but all it did was reference to its own cell.

Brian

 

[T. Valko]

The hyperlink would work great. How would install this? I just tried inserting a hyperlink, but all it did was reference to its own cell.

Brian

Here's a small sample file that demonstrates this.

ZZZffgilby2.xls 16kb

http://cjoint.com/?AHxeeiQoVLI

 

[ffgilby]

Got it!! This is just what I was looking for. Thanks for all of your help with this. I am sure I will think of something else to add to the spreadsheet, but as for now, thank you very much.

Brian

 

[T. Valko]

Got it!! This is just what I was looking for. Thanks for all of your help with this. I am sure I will think of something else to add to the spreadsheet, but as for now, thank you very much.

Brian

Good deal. Thanks for the feedback!