List Matching Items for Selection

Pick a region from the drop down list, and see a list of employees from that region. There are no macros, just INDEX/MATCH and other formulas. Download the free workbook.

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

Demo: List Matching Items

This animated screen shot shows how this technique works.

• Select a Region name from the drop down list
• Employees from that region are listed in the Excel table

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. There are setup instructions below.

Create 2 Lists

In your workbook, create a sheet named Lists. On this sheet, you'll start with 2 lists - for Regions and Employees.

NOTE: To save time, download the "Start" workbook. It has unformatted lists of regions and employees, so you won't have to type those.

Regions

To start the Regions list:

1. In cell G3, type the heading, Region
2. Format the heading with Bold font
3. In cells G4:G6, type the 3 regions names - East, Central, West.

Employees

To start the Employees list:

1. In cells I3 and J3, type the headings - Employee, Region.
2. Format the headings with Bold font
3. Starting in cell I4, type a short list of employee names
4. In column J, type a region name for each employee (East, Central or West)

Format Lists as Tables

Next, follow these steps, to format each list as an Excel Table. This makes your list dynamic -- the list size will adjust automatically if you add or remove items, so your drop down will show the entire list.

For both of the tables, follow these steps to format the list as a named Excel table:

1. Select a cell in the list, and on the Home tab, click Format as Table
   • 
2. Click on one of the Table Styles
3. Add a check mark for My table has headers
4. Click OK
• 

Next, name each of the tables:

1. With a cell in the table selected, click the Table Design tab at the top of Excel
2. In the Table Name box, type a name for the table
• In this example, the tables are named tblReg and tblEmp
• 

Create Named Ranges

Next, follow these steps to create named ranges, based on the Region and Employee tables. Later, you'll use these names for a drop down, and in formulas.

Name the Region List

1. Select the region names in the Region table (cells G4:G6), but not the table heading.
2. Click in the Name box, to the left of the formula bar
3. Type a one-word name for the list -- RegList
4. Press the Enter key, to complete the name.
   • 

Name the Employee List

Next, follow these steps to name the first column in the Employee table.

1. Select the employee names in column I (don't include the heading or regions)
2. Click in the Name box, to the left of the formula bar
3. Type a one-word name for the list -- EmpList.
4. Press the Enter key, to complete the name.

Add Region Drop Down

Next, you'll add a drop down list of Regions, using data validation.

1. In cell A2, type a heading - Select a Region:"
2. Select cell B3, and format it with an outside border and light green fill.
3. With cell B3 selected, click the Data tab, then click Data Validation.
4. From the Allow drop-down list, choose List
5. In the Source box, type an equal sign and the list name: =RegList
6. Click OK, to complete the data validation setup.
• 

Name and Test the Drop Down

Next, you'll choose a region from the drop down, and name the cell.

1. Select cell B3, and choose East from the drop down list.
2. Click in the Name box, to the left of the formula bar
3. Type a one-word name for cell B3 -- RegSel
4. Press the Enter key, to complete the name.

Add Numbers in Employee Table

Next, you'll add a column in the Employees table, to number the employees from the selected Region.

1. In cell K3, type the heading, ListNum
• The table will expand automatically, to include the new column
2. In cell K4, type this formula, to number employees from the selected Region:
   • =IF(J4<>RegSel,"",SUM(MAX(K$3:K3),1))
3. Press Enter, and the formula will automatically fill down to the last row in the Employee table
4. (optional) Centre the numbers in the ListNum column, and change the fill colour to light grey, to show that it contains formulas

How the Formula Works

Here's how the formula works, to number the employees from the selected region

=IF(J4<>RegSel,"",SUM(MAX(K$3:K3),1))

1. First, the IF function compares the Region name in cell J4 to the region name in cell B3 (RegSel)
• =IF(J4<>RegSel,
2. If the values are not equal (<>), the result is an empty string ("")
3. If the values are equal, MAX and SUM return the next number in the sequence:

• The MAX function finds the highest value in the rows above the current row, starting from row 3
  • MAX(K$3:K3)
• SUM function adds 1 to the MAX result
  • SUM(MAX(K$3:K3),1)

Add Region Count Formula

Next, you'll add a formula to count the number of employees in the selected region.

1. In cell I1, type a label - # in Region - and right-align the cell.
2. In cell J1, enter this formula, to return the highest number from the ListNum column:
• =MAX(tblEmp[ListNum])
3. Press Enter, and the formula will automatically fill down to the last row in the Employee table
4. (optional) Centre the number in cell J1, change fill colour to light grey, add an outside border

Then, follow these steps to name cell J1:

1. Select cell J1
2. Click in the Name box, to the left of the formula bar
3. Type a one-word name for cell B3 -- RegNum
4. Press the Enter key, to complete the name.

Create Matching Items List

The final step is to create a named Excel table, with a list of employees from the selected region

To start the Matching Items table:

1. In cells D3 and E3, type the headings - Num, Emp.
2. With one of the heading cells selected, on the Home tab, click Format as Table
3. Click on one of the Table Styles
4. Add a check mark for My table has headers, and click OK
5. This table should have at least as many rows as the Employee table, so point to the table handle at the bottom of cell E4, and drag down to the row where the Employee table ends (or below that)
6. (optional) In cell D2, type a heading - Region Employees

Next, to name the table:

1. With a cell in the table selected, click the Table Design tab at the top of Excel
2. In the Table Name box, type a name for the table - tblEmpReg

Number Formula

Next, you add a formula in the Num column, to create a list of numbers

1. In cell D4, type this formula, and press Enter
• =IF(D3="","", IF(D3=RegNum,"", SUM(MAX(D$3:D3),1)))
2. The formula will automatically fill down to the last row, with numbers for each employee in the selected region

In the sample file, there are 2 employees in the East region, so the numbers 1 and 2 are listed.

How the Formula Works

This formula is similar to the number formula in the Employee table, but it has 2 IF functions, instead of just 1.

=IF(D3="","", IF(D3=RegNum,"", SUM(MAX(D$3:D3),1)))

1. The first IF function checks the cell above, to see if it has an empty string ("")
• =IF(D3="",
2. If it does, the result is an empty string ("")
3. If not, the second IF checks the cell above, to see if it is equal to the number in cell J1 (RegNum)
• IF(D3=RegNum,
4. If it is equal, the result is an empty string ("")
5. If the values are not equal, MAX and SUM return the next number in the sequence:
• MAX function find the highest value in the rows above the current row, starting from row 3
  • MAX(D$3:D3)
• SUM function adds 1 to the MAX result
  • SUM(MAX(D$3:D3),1)

Employee Name Formula

Next, you add an INDEX/MATCH formula in the Emp column, to create a list of employee names

1. In cell E4, type this formula, and press Enter
• =IF(D4="","", IFERROR(INDEX(tblEmp[Employee], MATCH(D4,tblEmp[ListNum],0)),""))
2. The formula will automatically fill down to the last row, with numbers for each employee in the selected region

In the sample file, there are 2 employees in the East region, Al and Gil.

How the Formula Works

This formula looks for a number in the Employee table, and returns the name from that row.

=IF(D4="","", IFERROR(INDEX(tblEmp[Employee], MATCH(D4,tblEmp[ListNum],0)),""))

1. The first IF function checks the cell above, to see if it has an empty string ("")
• =IF(D4="",
2. If it does, the result is an empty string ("")
3. If it's not an empty string, the MATCH function tries to find the number in D4, somewhere in the ListNum column of the Employee table, and return the table row number
• MATCH(D4,tblEmp[ListNum],0)
4. Next, the INDEX function returns the employee name from that row in the Employee table
• INDEX(tblEmp[Employee],
5. Finally, the IFERROR function returns an empty string (""), if the INDEX function result is an error:
   • IFERROR(...,"")

Test the List

To test the Matching Items List, follow these steps

1. Select cell B3
2. Choose a different region from the drop down list

The following worksheet formulas will show results based on which region was selected:

• ListNum column in Employee table
• Cell J1 (RegNum)
• Num and Emp columns in Matching Items table

Get the Sample Files

NOTE: These zipped files are in xlsx format, and do not contain any macros.

1. Start: To follow along with the tutorial steps, download the Matching Items - Start workbook. 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
2. Completed: To see the completed workbook, download the Matching Items - Completed sample file. It has all of the formulas, names and table formatting

More Tutorials

Drop Down List

Create a Named Range

 IFERROR

INDEX/MATCH