Written by Debra Dalgleish from Contextures Blog
Today’s video shows how to set up a matching items list – select a region name, and the list shows all employees who work in that region. There are no macros, just a data validation list and a few formulas.
NOTE: If you have a version of Excel with dynamic arrays (Excel for Office 365), use dynamic arrays to create the list of matching items instead. (video below)
Show Matching Items
This animated screen shot shows how the matching items list works.
- Select a Region name from the drop down list
- Employees from that region are listed in the Excel table
Video: List Matching Items
This video shows how to set up the Excel tables, and the drop down list. Then, see how to add the formulas, and learn how those formulas work.
- 00:00 Introduction
- 01:01 Create 2 Lists
- 01:26 Named Tables
- 02:08 Name the Tables
- 02:48 Named Ranges
- 03:41 Drop Down List
- 04:50 Employee Numbers
- 07:31 Number in Region
- 08:44 Matching Items List
- 09:29 Number Formula
- 11:48 Employee Name Formula
- 13:47 Get the Workbook
Detailed Steps and Workbook
To see the detailed written steps, and to get the sample workbook, go to the Matching List Items page on my Contextures site.
There are two versions of the Excel file, so download one or both of them:
- Start: It has unformatted lists of regions and employees, so you don’t have to type those. None of the formulas, names or table formatting have been added
- Completed: It has the completed example, with all of the formulas, names and table formatting
Video: Matching Items – Dynamic Arrays
If your version of Excel has dynamic arrays, use those new functions to create the matching items list.
NOTE: Dynamic arrays are available in Microsoft 365 plans.
This video shows the steps, and get the written steps and sample file on the Dependent Drop Down – Dynamic Arrays page.
- 0:00 Introduction
- 0:31 Create a Unique List of Regions
- 2:38 Make a Region Drop Down
- 3:30 Create an Employee List
- 5:50 Make an Employee Drop Down
- 7:35 Get the Workbook
Show List of Matching Items in Excel
Show List of Matching Items in Excel is a post from Contextures Blog and is not allowed to be copied to other sites