There is no straight way to filter as you type in Google Sheets. You may want to depend on a workaround that I can say ‘almost perfect’ but not 100%. In Excel, you may have seen people filtering a table as they start typing their keyword in a search field.

To filter as you type, you must be able to get the value of the search filed in real time in another cell. That value you can feed to a Filter formula then.

Unfortunately, that is not possible in Google Sheets. So we have to depend on a workaround.

In Excel, the Combo Box (ActiveX Control) can be used as a search field which can transfer the value entering into it to another cell in real-time.

As far as I know, Google Sheets doesn’t have the Combo Box feature similar to Excel. So let’s go to my workaround. It would work like this.

Source Data:

Filter Rows As You Type in Google Sheets: I have set the filter to the third column that contains the “Source Country”. If you want, it’s easy to switch that column later.

Want to see this in action? Please check this Sheet – Search Suggestion.

Steps to Extract Rows As You Type in Google Sheets

Since the Combo Box (ActiveX Control) is not available in Google Sheets to use as the search field we have to find an alternative.

Fortunately, unlike in Excel, Google Sheets Data Validation “list from a range” offers search suggestion in real time.

This search suggestion has one peculiarity. It can suggest with the starting letter of any word in a phrase in a cell.

As you can see, typing the letter “e” will suggest all the items with the word “East” even though it’s the second word in the phrase. I am going to make use of this feature in our live filter.

Actually, the filter that I am going to make is not doing any real-time filtering. You will see the filter as a search suggestion as above in real time.

When you press enter or click any other cell, the same search suggestion will be extracted physically.

How to Create a Search Suggestion Drop-Down in Sheets

Since our data is spread across several rows and 3 columns, first we must combine the values in columns to a single column. So that we can use that in the drop-down as a ‘multi-column’ search suggestion.

My sample data is in F1:H16. Please apply this formula in cell I2.

=ArrayFormula(F2:F16&rept(".",10)&G2:G16&rept(".",10)&H2:H16)

If you have a growing list, then better use this formula. The above formula is only for a limited range.

=ArrayFormula(if(len(F2:F),F2:F&rept(".",10)&G2:G&rept(".",10)&H2:H,))

This will join the values in column F with J and H and place the dot symbol as the separator 10 times. I have used the REPT function to repeat the dot. You can change the dot symbol to hyphen and also the number of repetitions of it.

We can’t use space as the column separator as the Data Validation will eliminate extra space characters by default.

I am going to insert a search suggestion drop-down box using the above formula output.

1. Merge the cell A1:C1 (Format > Merge Cell > Merge All).
2. Then, in that cell, go to Data > Data Validation and create the drop-down box as below and “Save”.

Type the few starting letters of any country name available in the list. You can see that the corresponding row values appear in the Suggestion.

Filter Formula to Filter Rows Based on Drop-Down Search Suggestion

Now what we want is a filter formula that can filter the country names based on the value you input in the search field (drop-down).

Google Sheets FILTER function can’t directly take wildcards. We want to filter column H based on the partial matching value in the search field. Is there any alternative?

Yes! We can use REGEXMATCH in the condition parameter in the FILTER to filter rows based on the partial match.

Similar: Regexmatch in Filter Criteria in Google Sheets [Examples].

Enter this filter formula in cell A3. It takes the search key in the cell A1 (merged cell A1:C1) as the condition to filter the data in F:H.

=iferror({F1:H1;filter(F2:H,REGEXMATCH(H2:H,"^(?i)"&A1))})

I mean the REGEXMATCH formula inside the FILTER formula matches the search key in cell A1 in the column H. If found any match, the FILTER formula filters those rows.

The above is the workaround to filter rows as you type in Google Sheets.

Filter As You Type in Google Sheets – Changing the Filter Column

As already said, the drop-down search suggestion suggests words based on the first letter of any words in a phrase.

So if you want to change the filter column H (Source Country) to column F (Item Description) there is only change that you want to make. It is in the Filter formula in cell A3.

Just change REGEXMATCH text range H2:H to F2:F.

=iferror({F1:H1;filter(F2:H,REGEXMATCH(F2:F,"^(?i)"&A1))})

Additional Resources:

1. How to Create a Search Box Using Query in Google Sheets.
2. How to Create a Simple Multi-Column Search Box in Google Sheets.
3. Google Sheets: How to Get an All Selection Option in a Drop-down.
4. Create a Drop-Down to Filter Data From Rows and Columns.
5. Multi-Row Dynamic Dependent Drop Down List in Google Sheets.
6. Auto Populate Information Based on Drop down Selection in Google Sheets.