Searching for Data in Excel
Are you looking for the “peanut butter and chocolate” of workplace efficiency: how to combine the power of Macros with the convenience of search?
As of Office 2013, Excel does not provide an easy way to include a variable search term in the execution of a series of tasks recorded in a macro. By easy, we mean a default that can be specified to open a search box and then execute tasks based on the input. It is possible to create macros that do this by writing custom VBA code, but what can you do if you don’t want to hire a VBA programmer or go learn VBA yourself?
The following ideas address several possible alternatives for common scenarios that might send someone looking to include a search term in a macro. These steps will apply to Excel 2013. Images were taken using Excel 2013 on the Windows 7 OS.
Scenario 1: You receive a weekly report from your division manager. You like to go through and highlight all references to your department so your team can easily spot the information they are most interested in.
Solution – Use Find/Replace in a Macro: When you always search for the same thing, this is one time when search and macros go together well. To follow using our example, download Excel Search, SalesByPeriod Sheet.
- Click Record macro in the Code group on the Developer
- Enter a name, description, and shortcut key for your macro.
- Perform a search for the departments that you want to edit by using CTRL+F and the Find/Replace dialog box. Repeat for as many different searches as you need to make, then click Stop Recording to end and save your macro.
When you run your macro, Excel will use the same Find/Replace terms that you used when recording. This can save you lots of time if you perform several searches in a row and make multiple formatting changes with each Replace.
Notice that when you run the macro, you do not see the Find and Replace dialog box. The macro just acts upon the data.
Scenario 2: You receive a spreadsheet that contains all the information that people have submitted to an online web form, including a list of volunteer tasks they are interested in. You would like to show that everyone who listed “gardening” in their skill list should receive a follow up phone call and everyone who does not should receive a postcard.
Solution – Use the SEARCH function in a formula: The SEARCH function in Excel allows you to search for a character or string of text within a larger string of text and return the position of the text the function finds. To follow using our example, download Excel Search, SEARCH function Sheet.
SEARCH function syntax: SEARCH( search_text, within_text, [start_num] )
See that in the example, we searched for the phrase “garden” which returns the number 31 (the position that the text “garden” was found) in K3 and an error in K4 (because the text was not found).
Now let’s build on this formula to make the search more useful. Our final formula combines SEARCH with the IF function and the ISERROR function to return a True/False variation that replaces the error message with the text “Send Postcard” when “garden” is not found and the position number with “Call” when “garden” is found in the volunteer’s list of skills and interests.
Scenario 3: You are a sales person at a Fitness Club that has developed a complex membership pricing system based on the age of the member. You would like a way to quickly search the tables of fees by the age of a lead to be able to offer them the correct membership fee amount.
Solution – Use VLOOKUP
VLOOKUP lets you search for a value in a pricing table to quickly return the membership fee based on an age you either enter, or that has been calculated from your birthdate information. To follow using our example, download Excel Search, VLOOKUP Sheet.
Create a table that specifies the cost of membership per age. Then, create a small table with one cell that you will type the age you want to look up and one cell that will return the value using the VLOOKUP function. In our example, we’ve highlighted the “search” box yellow to be easy to find and use.
Type in any age in the designated Enter box to see updated results:
These examples represent only a few ideas for how to give Excel a little more “search” functionality using its innate set of tools. If you find, however, that you often require complicated queries of your information, you might look into whether you really need your information in a database instead of Excel.
