3

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.

Improve this question
3
  • Welcome to Ask Different! :) What you want to do is possible by using formulas/functions to do all the work within Numbers. I’d be happy to post an answer on how to do this, but it’d be a lot easier for me to explain how to do this if you could edit your question and advise the names of your sheets (i.e. what you’re calling tabs), which one contains the data you want to search, and the range the data is located in. I also need to know where you’d want the search box to be (i.e. just give me a cell reference). This will mean I can provide the exact formulas you’ll need for your scenario.
    – Monomeeth ♦
    Apr 3, 2019 at 10:27
  • Thanks for adding that info. Can I ask what, if anything, you already have in your Search sheet? Or is it blank?
    – Monomeeth ♦
    Apr 3, 2019 at 10:44
  • It is blank except that cell A5 contains the words "Enter search term:" and cell B5 has a thick black border around it. @Monomeeth
    – yasmine
    Apr 3, 2019 at 11:07

1 Answer 1

Reset to default
6

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.

Improve this answer
2
  • This is freakin amazing! I've followed your steps and have up to 50 search results. Everything works a charm. The only problem I have is that clearing the search box doesn't clear the search results. Instead they show the first 50 items in my data source. Any ideas how I can change this so the search results are blank when there's nothing in the search field?
    – yasmine
    Apr 4, 2019 at 3:37
  • Hmm, if you were doing this in Excel I'd say you could use Conditional Formatting to hide the search results if cell B5 (your search field) is blank. Unfortunately I don't think you can use the Conditional Highlighting feature in Numbers to achieve the same thing, but I'll have a look and will update my answer.
    – Monomeeth ♦
    Apr 4, 2019 at 9:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .