Show List of Matching Items in Excel

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

List matching items demo

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.

Video Timeline

  • 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.

Video Timeline:

  • 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

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

Original source:

Leave a Reply

Close Menu