Criteria search

ffgilby · Aug 15, 2011

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 · Aug 17, 2011

Whew, got it. Thanks Biff. I was able to format all the search cells to be "Number" format, and the search worked!! Thank you very much.
How did you set up the colored formatting for the row matching the search criteria?
Now, is there a way of inserting a page break under Row3 (I changed a few rows around at the top) and having the sheet scroll through the rows to the exact row having the match, putting it directly below Row3?

T. Valko · Aug 17, 2011

ffgilby said:
Whew, got it. Thanks Biff. I was able to format all the search cells to be "Number" format, and the search worked!! Thank you very much.

Good deal!

How did you set up the colored formatting for the row matching the search criteria?

I did that using conditional formatting. I did it so that when you tested the file it would be easy to see where the match, if any, was located.

Now, is there a way of inserting a page break under Row3 (I changed a few rows around at the top) and having the sheet scroll through the rows to the exact row having the match, putting it directly below Row3?

I think you'll need VBA programming to accomplish that. I'm not much of a programmer so someone else will need to help you with that.

ffgilby · Aug 17, 2011

All Edits

<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: 64px"> <col style="WIDTH: 109px"> <col style="WIDTH: 109px"></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="FONT-SIZE: 12pt; FONT-WEIGHT: bold">Column1/Column 2 Edits</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="HEIGHT: 84px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: right">Column 1</td> <td style="TEXT-ALIGN: right">Column 2</td> <td style="TEXT-ALIGN: left; FONT-WEIGHT: bold">Modifier
0=not allowed
1=allowed
9=not applicable</td> <td style="FONT-WEIGHT: bold">
</td></tr> <tr style="HEIGHT: 34px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</td> <td style="TEXT-ALIGN: right; FONT-SIZE: 14pt; FONT-WEIGHT: bold">10021</td> <td style="TEXT-ALIGN: right; FONT-SIZE: 14pt; FONT-WEIGHT: bold">36000</td> <td style="TEXT-ALIGN: left; BACKGROUND-COLOR: #92d050; FONT-SIZE: 14pt; FONT-WEIGHT: bold">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">C8950</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">C8952</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">G0345</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">G0347</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">G0351</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">G0353</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">G0354</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">J2001</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">0213T</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">0216T</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">19290</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">36000</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">36410</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">37202</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">62318</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">62319</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64415</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64416</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64417</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64450</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64470</td> <td style="TEXT-ALIGN: left">1</td> <td>
</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</td> <td style="TEXT-ALIGN: right">10021</td> <td style="TEXT-ALIGN: right">64475</td> <td style="TEXT-ALIGN: left">1</td> <td>
</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:D52056,MATCH(1,IF(B4:B52056=B3,IF(C4:C52056=C3,1)),0))}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>

Here is a look at what I have so far. I was curious if you or anyone else knew how to put the cell number that contains the match in D3 (if exists) in cell E3, so as to have a row/cell to reference. I.E., E3 would contain "E15", the match for criteria entered in B3 & C3.
Once again, thanks for everyone's help.

T. Valko · Aug 18, 2011

ffgilby said:
All Edits

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 109px"><COL style="WIDTH: 109px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>

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

</TD><TD>

</TD><TD>

</TD></TR><TR style="HEIGHT: 84px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">Column 1</TD><TD style="TEXT-ALIGN: right">Column 2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Modifier
0=not allowed
1=allowed
9=not applicable
</TD><TD style="FONT-WEIGHT: bold">

</TD></TR><TR style="HEIGHT: 34px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; TEXT-ALIGN: right">10021</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; TEXT-ALIGN: right">36000</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 14pt; BACKGROUND-COLOR: #92d050; TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">C8950</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">C8952</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">G0345</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">G0347</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

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

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">G0353</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">G0354</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">J2001</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">0213T</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">0216T</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">19290</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">36000</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">36410</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">37202</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

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

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">62319</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64415</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64416</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64417</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64450</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64470</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">10021</TD><TD style="TEXT-ALIGN: right">64475</TD><TD style="TEXT-ALIGN: left">1</TD><TD>

</TD></TR></TBODY></TABLE>
<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:D52056,MATCH(1,IF(B4:B52056=B3,IF(C4:C52056=C3,1)),0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>

Here is a look at what I have so far. I was curious if you or anyone else knew how to put the cell number that contains the match in D3 (if exists) in cell E3, so as to have a row/cell to reference. I.E., E3 would contain "E15", the match for criteria entered in B3 & C3.
Once again, thanks for everyone's help.

Try this array formula**:

=IF(D3="","","E"&MATCH(1,IF(B4:B52056=B3,IF(C4:C52056=C3,1)),0)+ROW(B4)-1)

** 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.

taurean · Aug 18, 2011

1. Freeze Pane so that first 3 rows will remain visible.
2. Create a button for macro as shown here in this video
http://www.youtube.com/watch?v=4ji19G6rTm8
3. Paste the following code:

Code:

For i = 4 To Range("B" & Rows.Count).End(xlUp).Row
    If Range("B" & i).Value = Range("B3").Value And _
    Range("C" & i).Value = Range("C3").Value Then
    MsgBox "Match found at Row : " & i
    Range("D3").Value = Range("D" & i).Value
    Range("E3").Value = Range("E" & i).Address
    Rows(i).Select
    Exit Sub
    End If
    
    If i = Range("B" & Rows.Count).End(xlUp).Row And _
    (Range("B" & i).Value <> Range("B3").Value Or _
    Range("C" & i).Value <> Range("B3").Value) Then
    MsgBox "There are no matches for current combination!"
    Range("D3").Value = ""
    Range("E3").Value = ""
    End If
Next i

ffgilby · Aug 20, 2011

Shrivallabha, thanks for the macro code. I was able to get the code to work, and it popped up a window with the corresponding cell range.
What I was looking for was a way to automatically scroll to the cell, as pictured below.
-- removed inline image ---

MrVillareal · Aug 21, 2011

Hi try using filter or advanced filter..

taurean · Aug 21, 2011

ffgilby said:
Shrivallabha, thanks for the macro code. I was able to get the code to work, and it popped up a window with the corresponding cell range.
What I was looking for was a way to automatically scroll to the cell, as pictured below.
-- removed inline image ---

I can't see the picture. But your requirements are fulfilled see below:
The macro retrieves value (when B & C match) in D3 through

Code:

Range("D3").Value = Range("D" & i).Value

The row address comes in E3 through

Code:

Range("E3").Value = Range("E" & i).Address

The row where data is found will be selected through:

Code:

Rows(i).Select

So it brings it in the visible area.
Where have you located the macro button (it should be in first 3 rows)?

T. Valko · Aug 21, 2011

ffgilby said:
Shrivallabha, thanks for the macro code. I was able to get the code to work, and it popped up a window with the corresponding cell range.
What I was looking for was a way to automatically scroll to the cell, as pictured below.
-- removed inline image ---

Your pictures didn't get posted.

Will a hyperlink work?

ffgilby · Aug 21, 2011

Sorry for the previous posts, the HTML copying function was not working. Here is the link to my document. If the file is too big to open, let me know and I can downsize the document. Thanks, Brian

https://skydrive.live.com/redir.aspx?cid=f7d35aad314f4ab0&resid=F7D35AAD314F4AB0!126

Criteria search

ffgilby

New Member

ffgilby

New Member

Excel Facts

T. Valko

Well-known Member

ffgilby

New Member

T. Valko

Well-known Member

taurean

Well-known Member

ffgilby

New Member

MrVillareal

Well-known Member

taurean

Well-known Member

T. Valko

Well-known Member

ffgilby

New Member

Similar threads

Forum statistics

Share this page

Criteria search

New Member

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock