Combobox with search suggestions I created a combobox of which the list of options updates whenever you type in the box. Just a bit like entering a search in google works with suggestions.
I followed this tutorial to do so: https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/ 

There are 4 columns of data in the sheet that make this work, combined with a named range, and a short piece of VBA to update the combobox. The combobox is in linked to cell B3. And data is in E:H starting at row 3.


First column includes the options for the combobox. There are 5619 possible options to choose from.
Second column has the following formula to determine if the entry of the combobox is in the text of the same row in the first column: =--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),""))
Third column has the following formula to establish the amount of matches found: =IF(F3=1,COUNTIF($F$3:F3,1),"")
Fourth column has the following formula to return the list of possible options based on the value typed in the combobox: =IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")


The code to update the combobox is as follows:

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub


With DropDownList Being a named range that selects all the names in the fourth column.

The problem I face here is purely performance. It is really slow and unresponsive on a PC. Let alone deployed on a mobile device, which I intend to. 

The problem is ofcourse within the fact that there are 5600+ options for the combobox, and therefore 4*5600 formula's to be recalculated at every character entered in the combobox. I was wondering if there is a way to do with less helper columns, and maybe more VBA to reduce the calculation times required?
To combine the second and third column into only 1 helper column I deviced the following formula's:

In the first cell of the second column I put the following formula: =IF(ISNUMBER(SEARCH($B$3,E3,1))=FALSE,"",--ISNUMBER(SEARCH($B$3,E3,1))

In the second cell and down I wrote the following formula: =IF(--ISNUMBER(IFERROR(SEARCH($B$3,E4,1),""))=0,"",COUNT($E$3:$E3)+1)