All steps in this answer need to be carried out on your Search sheet. There is no need to touch your Data sheet.
First make sure you have at least 4,506 rows available in your Search Sheet. The reason for this number is that we're allowing enough room below your search field for all of the corresponding rows of data you have.
In Cell G7 enter the following formula and press return:
NOTE: This formula is just telling Numbers to populate Cell G7 with whatever data is in Cell B2 in your Data sheet, since this is the first cell containing the data you want to be searchable.
Now select Cell G7 and you will notice a yellow handle at bottom centre of the cell. Drag this down to row 4,506. This will copy the formula down all the rows, incrementally changing each one to refer to Cell B3, B4, etc.
In Cell F7 enter the following formula and press return:
$B$5 refers to your search field. You will need to change this if you decide to use a different cell for your search box. The
5000can be 10000 or 100000, it doesn't really matter.
Now select Cell F7 and drag the yellow handle down to row 4,506. Once again this copies the formula down all the rows, incrementally changing each one.
In Cell E7 enter the following formula and press return:
Now select Cell E7 and drag the yellow handle down to row 4,506. Once again this copies the formula down all the rows, incrementally changing each one.
In Cell A7 enter the value '1', in Cell A8 the value '2', and so on for whatever number of search results you want returned. For example, if you want to have up to 10 search results displayed, then work your way down to Cell A16 which will contain the value ’10'.
In Cell B7 enter the following formula and press return:
- Now select Cell B7 and drag the yellow handle down to whatever row corresponds with the last number you entered at Step 8 above. For example, if you opted for 10 search results, you’d drag the handle down to Cell B16.
Now all of the hard work is done. If you place the cursor into Cell B5 you should be able to enter a search term in order to conduct your search. Assuming you wanted up to 10 search results, when you enter a search term in Cell B5 you will get anywhere between 0 and 10 search results, even if your data contains more than 10. So make sure at Step 8 you’ve allowed enough rows for the potential number of search results.
Below is an animated GIF of how this works in a dummy workbook I created that resembles your description:
You may want to hide columns E, F and G so you don’t see the workings of the search functionality. Alternatively, you could format these columns so that all text matches the background colour of the cell, so that you don’t see all of the clutter.
If over time you add data to your Data sheet, then the number of rows in your Search sheet will need to change, as will the
4506 used in the formulas.