Search Sheets Help

Dishboy09

New Member
Joined
Jun 11, 2017
Messages
24
I have an idea for a search sheet that i have no idea how to create, and am looking for help.

I would like Sheet 1 to just be a search box and you can type in an address, and below it will be a table that will show the results that match your search. The matched search data will be stored on sheet 2 with a large list of addresses.

How would i go about creating something like this??


Thanks!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Hi,

Here's a formula version, it will return rows that match from partial street address. Adjust the ranges to the size of your data set on sheet 2.

On the Search sheet make sure the formula is copied past maybe 500 rows past what the total results should be.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;;">Partial Search</td><td style="font-weight: bold;;">Matches #</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Mar</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;;">Search Results</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Street</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Suburb</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Postcode</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">10 Mary St1</td><td style=";">Port Adelaide1</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">12 March St1</td><td style=";">Port Adelaide3</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">13 Mary St1</td><td style=";">Port Adelaide4</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">4 Margeret</td><td style=";">Port Adelaide5</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">16 Mary St1</td><td style=";">Port Adelaide7</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">66 Marigold Ave</td><td style=";">Port Adelaide10</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">20 Mary St1</td><td style=";">Port Adelaide11</td><td style="text-align: right;;">5015</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=COUNTIF(<font color="Blue">Sheet2!D:D,1</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">Sheet1!$B$8:B8</font>)>$C$3,"",INDEX(<font color="Red">Sheet2!A$2:A$10,SMALL(<font color="Green">IF(<font color="Purple">Sheet2!$D$2:$D$10=1,ROW(<font color="Teal">Sheet2!$A$2:$A$10</font>)-ROW(<font color="Teal">Sheet2!$A$2</font>)+1</font>),ROWS(<font color="Purple">Sheet1!$B$8:B8</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #FFFFFF;background-color: #4472C4;;">Street</td><td style="color: #FFFFFF;background-color: #4472C4;;">Suburb</td><td style="color: #FFFFFF;background-color: #4472C4;;">Postcode</td><td style="color: #FFFFFF;background-color: #4472C4;;">Partial Match</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10 Mary St1</td><td style=";">Port Adelaide1</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">2 Mickey Place</td><td style=";">Disneyland</td><td style="text-align: right;;">5015</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">12 March St1</td><td style=";">Port Adelaide3</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">13 Mary St1</td><td style=";">Port Adelaide4</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">4 Margeret</td><td style=";">Port Adelaide5</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">10 Peppercorn Drive</td><td style=";">Salisbury</td><td style="text-align: right;;">5015</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">16 Mary St1</td><td style=";">Port Adelaide7</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">66 Marigold Ave</td><td style=";">Port Adelaide10</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">20 Mary St1</td><td style=";">Port Adelaide11</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">Sheet1!$B$3,Sheet2!A2</font>)</font>),1,""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Dishboy09

New Member
Joined
Jun 11, 2017
Messages
24
Hi,

Here's a formula version, it will return rows that match from partial street address. Adjust the ranges to the size of your data set on sheet 2.

On the Search sheet make sure the formula is copied past maybe 500 rows past what the total results should be.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;;">Partial Search</td><td style="font-weight: bold;;">Matches #</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Mar</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;;">Search Results</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Street</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Suburb</td><td style="color: #FFFFFF;background-color: #ED7D31;;">Postcode</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">10 Mary St1</td><td style=";">Port Adelaide1</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">12 March St1</td><td style=";">Port Adelaide3</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">13 Mary St1</td><td style=";">Port Adelaide4</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">4 Margeret</td><td style=";">Port Adelaide5</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">16 Mary St1</td><td style=";">Port Adelaide7</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">66 Marigold Ave</td><td style=";">Port Adelaide10</td><td style="text-align: right;;">5015</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">20 Mary St1</td><td style=";">Port Adelaide11</td><td style="text-align: right;;">5015</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=COUNTIF(<font color="Blue">Sheet2!D:D,1</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B8</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">Sheet1!$B$8:B8</font>)>$C$3,"",INDEX(<font color="Red">Sheet2!A$2:A$10,SMALL(<font color="Green">IF(<font color="Purple">Sheet2!$D$2:$D$10=1,ROW(<font color="Teal">Sheet2!$A$2:$A$10</font>)-ROW(<font color="Teal">Sheet2!$A$2</font>)+1</font>),ROWS(<font color="Purple">Sheet1!$B$8:B8</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #FFFFFF;background-color: #4472C4;;">Street</td><td style="color: #FFFFFF;background-color: #4472C4;;">Suburb</td><td style="color: #FFFFFF;background-color: #4472C4;;">Postcode</td><td style="color: #FFFFFF;background-color: #4472C4;;">Partial Match</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">10 Mary St1</td><td style=";">Port Adelaide1</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">2 Mickey Place</td><td style=";">Disneyland</td><td style="text-align: right;;">5015</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">12 March St1</td><td style=";">Port Adelaide3</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">13 Mary St1</td><td style=";">Port Adelaide4</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">4 Margeret</td><td style=";">Port Adelaide5</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">10 Peppercorn Drive</td><td style=";">Salisbury</td><td style="text-align: right;;">5015</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">16 Mary St1</td><td style=";">Port Adelaide7</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">66 Marigold Ave</td><td style=";">Port Adelaide10</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">20 Mary St1</td><td style=";">Port Adelaide11</td><td style="text-align: right;;">5015</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">Sheet1!$B$3,Sheet2!A2</font>)</font>),1,""</font>)</td></tr></tbody></table></td></tr></table><br />

I really appreciate the response, but i have no idea what to do with this or where to put it to make it work?
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
When the website was upgraded this code doesnt work. I'll post a link to my mock up sheet for you soon.
 

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Here's a link to the sample version I made,

Adjust the ranges to the size of your data set on sheet 2.

On the Search sheet make sure the formula is copied past maybe 500 rows past what the total results should be.


Search Sheets Help_Dishboy09.xlsx
 

Watch MrExcel Video

Forum statistics

Threads
1,099,548
Messages
5,469,360
Members
406,647
Latest member
ssinovec

This Week's Hot Topics

Top