1. Building a search box
Google had better watch out because you are about to learn how to build your own search engine. By the end of this lesson, you will know how to use an input box in your spreadsheet to highlight cells that contain the word you entered.
2. Maintaining bridges
Now you are a civil engineer and your job is to maintain the bridges for the city of Pittsburgh, Pennsylvania. The city is built in the Allegheny Mountains and lies where two rivers meet to form the Ohio River.
3. Highlighting rivers
Let's say that this month, you are focusing only on bridges that cross the Monongahela River. You could apply a simple conditional formatting rule, as shown in this picture, to highlight all of the cells with the river's name in it. However, we are going to make this more flexible so that anyone, even if they don't know how to use conditional formatting, can change the name of the river being highlighted.
4. Input box
We start by adding a few cells to the right of the data. There is no code associated with these cells yet. Styling has been added to them for emphasis and to make it clear that cell E3 is waiting for data.
5. Custom formula
We could do a simple lookup like this to see if a row has the exact word that is typed into the input cell E3, which works if you type in the entire, exact word.
You can see that someone typed in the name of a river into the input box and the formula is highlighting each row that contains this river's name.
6. Parts of a word
Let's be honest here though. How many people in this world can spell Monongahela? We're going to craft a more forgiving search function with either of the aptly named functions called SEARCH or FIND. We'll take a quick look at these functions and then move it into our custom formula for the conditional formatting.
7. SEARCH and FIND
Both of these formulas will return a number greater than zero if they find a text string anywhere in a given cell. We are going to be using the SEARCH function, but just keep in mind that the FIND function is out there if you need it. FIND is case sensitive so it cares about capitalization.
8. SEARCH syntax
The SEARCH function takes two arguments. It simply wants to know what to search for and where to search for it. In exchange, it will either give you an error or a number value.
Sounds perfect, right? Since the conditional formatting works like a TRUE/FALSE question, we'll just write a formula that asks if this function is returning a number greater than zero or not, and it will do the highlighting for us.
Here is what it will look like in action. We are going to SEARCH for the letters Mon in the word Monongahela. The SEARCH function will return a 1 because the letters Mon start in the first position of the word Monongahela. Now it evaluates if the result is greater than zero, which is how we get to the TRUE/FALSE question that the conditional formatting needs to answer.
9. Search in action
The formula is looking for the value in the input box in column C in each row. This is done by combining the technique of highlighting an entire row with an input box and the SEARCH function.
10. Time to practice!
Let's practice this one to see if we can build our own search box. Here we go!