MacOS – Creating a search engine within Numbers on a Mac

macosnumberssearchsearch-engine

I want to know if it’s possible to create a search box directly within my Numbers document without the use of scripting, automator or terminal.

The idea is that users could enter search terms in the box and it’d return any matching results, just like using a search engine. The data to be searched is also within the same Numbers document, but in a separate tab.

I’m sorry if this question seems like I haven’t shown any research efforts, but so far I’ve searched within Numbers Help, Apple’s Numbers support pages and done general Google searches. These haven’t produced any results either way, so I think maybe what I want to do isn’t possible.

Can someone hear confirm either way? If possible, how do I achieve this?

MORE INFO

My Numbers document contains two tabs. The 1st tab is called Search and the 2nd tab is called Data.

I would like the search box to be in cell B5 of the Search tab.

The data being searched is located in Column B of the Data tab, from rows 2 to 4,500.

The search results should appear below the search box.

Best Answer

All steps in this answer need to be carried out on your Search sheet. There is no need to touch your Data sheet.

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

  2. In Cell G7 enter the following formula and press return:

    =Data::Table 1::B2

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.

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

  2. In Cell F7 enter the following formula and press return:

IFERROR(SEARCH($B$5,G7)+ROW()/5000,"")

NOTE: The $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.

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

  2. In Cell E7 enter the following formula and press return:

IFERROR(RANK(F7,$F$7:$F$4506,1),"")

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

  2. 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'.

  3. In Cell B7 enter the following formula and press return:

IFERROR(VLOOKUP(A7,$E$7:$G$4506,3,FALSE),"")

  1. 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:

NOTES:

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

Related Question