Create a search field in Excel in 5 minutes

A NEW VERSION OF THIS POST HAS BEEN PUBLISHED HERE: http://easy-excel.com/?p=158

Is it possible to create a search field in Excel, without using VBA?

Yes, and it’s easy!

We will use Conditional Formatting to highlight the fields that match the search string. For example, if you look at the table below, we want to highlight row 8, 11, 15 and 25 if we search for “RG”, because “RG” is part of the product name in those rows.

The secret is the SEARCH function. The SEARCH function looks for text within a text and returns its position. For example, the formula =SEARCH(“RD”,A5) will return 1, because it finds “RD” in the first position in the text in A5. If we search for “XX” instead, it would return #VALUE.

As we saw in a previous post, Conditional Formatting needs a TRUE or FALSE to determine whether or not to apply the formatting. The SEARCH function returns a number if it finds what we are looking for, so we simply put it inside the ISNUMBER function, which gives us a TRUE if the result of the SEARCH formula is a number:

=ISNUMBER(SEARCH($C$2,A5))

Select the cells you want to include in the search and click on Conditional Formatting from the Home ribbon and choose New Rule (Shortcut: Alt => H => L => N). Choose “Use a formula to determine which cells to format” and type the formula in the formula field.

If we type “RG” in the search field, all products with “RG” in the name will be highlighted! And it works with numbers too!


There is one problem with this formula, though. If you leave the search field empty, all cells will be highlighted. Here’s the workaround:

We use the AND function to add the additional criterion; that the search field must be empty for the function to be true. The syntax for “not empty” is <>”” (<> means not equal, and “” (2 double quotes) means empty or null). This is the formula we can use in Conditional Formatting to make it perfect:

=AND(ISNUMBER(SEARCH($C$2,A5)),$C$2<>“”)

___________________________________________________________________________________

Bonus tip 1: How can we find out how many matches we got? Try this formula:

=SUM(–(ISNUMBER(SEARCH($C$2,$A$5:$A$54))))

This is an array formula, so you have to close it with Ctrl+Shift+Enter

Bonus tip 2: Is it possible to create a list of results? Yes, but it’s not easy, so I’m not going to explain how it works in this post.

=IFERROR(IF(ROWS($F5:F5)<=SUM(–(ISNUMBER(SEARCH($C$2,$A$5:$A$54)))),INDEX($A$5:$A$54,SMALL(IF(ISNUMBER(SEARCH($C$2,$A$5:$A$54)),ROW($A$5:$A$54)-ROW($A$5)+1),ROWS(F$5:F5))),“”),“”)

(close with Ctrl+Shift+Enter and copy down)

Note: If you use comma as the decimal separator as default (applies to most non-English users) you have to replace the commas in the formulas with semicolons.

Author: Audun Danielsen

3 thoughts on “Create a search field in Excel in 5 minutes

  1. Pingback: Find Duplicates and Triplicates in Excel | Excel is easy!

  2. I Like the formulas but is it possible to have the search formula take you to the cell that is true. I have a Sheet that has 1500 rows of info. so I would like to search the sheet and be moved to the row or cell that has the correct info. that was searched for. I’m working in excel 2013

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s