Ultimately, if there is nothing to filter after cycling through all five columns, a message stating “No Results!” is displayed to the user. The formulas are the exact same except the Index function is referencing a different column number in each Filter function. Notice the Filter function is repeated 5 times for the 5 columns in our data set. Luckily, since the Filter function has an If_Empty input, we can simply execute another Filter function pointing to a new column if no results are found in the prior column.īelow is the formula used for our example.

There are 4 parts to this formula:Ĭolumn Looping - To get a column looping or cycling effect for our search engine, we will actually need to perform our Filter Function multiple times (one for each column we wish to search through). This logic can be contained in a single Array Formula. Because it points its column input to the output of your Option Buttons, it will allow the IsNumber/Search combination to only look at a single column within your data table.įinally, you will need to write a formula for the search engine itself. Index Function - This controls which column is getting searched. This allows us to perform partial-match or wildcard searches. IsNumber/Search Functions - Since the Filter Function does not have a wildcard capability, there is a creative workaround we can use to give us this functionality. There is an optional third input for if there are no search results found where we will return the text “No Results Found”. There are 3 parts to this formula:įilter Function - The Filter function allows you to perform a logic test within a column of a data range and return only the rows that pass the test.

Table Body Formula For Filtered Dataįinally, you will need to write a formula for the search engine itself. You will only need to do this one time as all Option Boxes on your spreadsheet will link to the same cell by default. Right-clicking one of your Option Buttons Lastly, you will need to link your Option Buttons to a cell on your spreadsheet.

Data forms for excel for mac