Excel has a lot of options for finding data, but a customized feature is often a bit friendly and easier to use.
Finding data in Excel can be a simple task. You can use the built-in Find and Filter features. The Find feature is easy to use, but you can't use the results. Filtering is easy enough to learn, but it isn't a feature your novice users will be comfortable using at first. In any case, you might want a custom route, one that requires no specialized knowledge to use; users can simply click. In this article, I'll show you a simple way to find a value by matching another. By using this custom find technique, you can provide a feature that won't tax your most inexperienced users. In addition, if you need a stable cell for referencing, this eliminates the inherent problems with Excel's Find and Filter features.
I'm using Excel 2013 and Windows 7. You can work with any simple data set or download the demonstration .xlsx or .xls file. We'll eventually use the Table object, which isn't supported by Excel 2003, but the initial setup will work in 2003.
Figure A shows a simple data. A ZIP Code is easily found in this small data set. In a large data set with hundreds of records, you'd need one of Excel's built-in features. As I already mentioned, the Find feature locates the data, but you can't reuse the found value. You could use Excel's Filter feature, but it's a bit awkward to use and can be confusing to the novice or unskilled Excel user.
We'll add a custom find feature to this simple data set.
Let's take a quick look at how filtering would work using the data set in Figure A:
- Click inside the data set.
- Click the Data tab.
- Click Filter in the Sort & Filter group. Doing so will display drop-downs in each header cell.
- To find the ZIP code for Matthews, click the Name drop-down, uncheck (Select All), and check Matthews (Figure B).
- Click OK (Figure C).
It's not difficult, and you can quickly teach most users to implement filtering. It's certainly a good option. On the other hand, it's difficult to reuse the filtered data--not impossible, but usually beyond the causal user's abilities. Instead, you might want to offer a custom find solution that allows the user to select a name from a list and an expression returns the appropriate ZIP code automatically. The biggest problem this simple technique solves is providing a stable cell reference that you can use in other expressions. Clear the filter before you continue by clicking Filter in the Sort & Filter group on the Data tab.
Create a list
If you're not familiar with Excel's Data Validation feature, you're about to see something impressive and that you can reuse in most of your Excel sheets. It's great for eliminating data input errors such as typos and invalid data. You control the list, and users must choose from it instead of entering data from the keyboard. There's no data input whatsoever.
We'll use Excel's Data Validation feature to display the names in column B. To create this list, do the following:
- Select the cell where you want to display the custom find control. In this case, that's B3.
- Click the Data tab.
- In the Data Tools group, click the Data Validation option, and then choose Data Validation from the drop-down if presented.
- In the resulting dialog, choose List from the Allow drop-down.
- Enter $B$6:$B$10 as the Source setting (Figure D).
- Click OK.
As you can in Figure E, you can limit user choices by forcing them to select a name from the validation control now in B3. This built-in feature is worth an article of its own!
Use Data Validation to limit input.
Find a match
To complete the custom find solution, you need an expression that matches the name selected in the list (cell B3) and then returns the corresponding ZIP code in column C, so enter the following expression into C3:
Don't worry if the expression initially returns #N/A. Choose a name from the validation control in B3, and the expression in C3 updates accordingly. For example, Figure F shows the result of choosing Matthews.
Choosing an item from the list displays the appropriate ZIP code.
To understand how this expression works, let's evaluate the above example. The MATCH() function returns the position of the matching item in column B. In this case, MATCH() returns 2. The INDEX() function uses the results of the MATCH() function to return the corresponding item in column C, which is 40065.
Limitations and cure
If you're working with a stable list, this is a great setup. If you're adding to the list, it falls a bit flat because neither the list nor the expression update as you add new records. You can change values in the existing dataset, but you can't add new records to it. Older versions of Excel require a dynamic range to support new records, and that's beyond the scope of this article.
If you need only one reason to update to the .xlsx format, the Table object is it. It's supported by Excel 2007 and later versions, and it's the answer to most of your dynamic needs. If you need to add items to the list, convert the data to a Table, and Excel takes care of the rest. To convert the example data set, click inside the data set and do the following:
- Click the Insert tab.
- In the Tables group, click Table.
- In the resulting dialog, check the My table has headers option (Figure G).
- Click OK.
Now, let's test the new Table by adding a record for Thompson with a ZIP code of 40234. Both the list (see Figure H) and the expression update, because the Table object is dynamic.
Referencing a Table, the list and expression support new records.
Once you convert your data to a Table object, you could use the Table's built-in filters, but you'll have to train users to implement them correctly. The upside to this custom find solution is that you won't have to train users, and the ZIP code value is in a stable cell that you can easily reference in other features and expressions.
It's a simple choice
The custom find list and expression isn't better than Excel's built-in features. Users will probably find the list easier to use than the Find or Filter features unless they're already familiar with them. Perhaps, most importantly, this solution provides a stable cell for referencing. On the other hand, a drop-down list works best with a small data set--and you can't sort the list, so it has its limitations. Even if you don't use this simple solution, you've learned about the Data Validation feature, which is beneficial for limiting data input and eliminating errors from your data.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers that I help. You can contact me at firstname.lastname@example.org.