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.
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:
In cell G3, type the heading, Region
Format the heading with Bold font
In cells G4:G6, type the 3 regions names - East, Central, West.
Employees
To start the Employees list:
In cells I3 and J3, type the headings - Employee, Region.
Format the headings with Bold font
Starting in cell I4, type a short list of employee names
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:
Select a cell in the list, and on the Home tab, click Format as Table
Click on one of the Table Styles
Add a check mark for My table has headers
Click OK
Next, name each of the tables:
With a cell in the table selected,
click the Table Design tab at the top of Excel
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
Select the region names in the Region table (cells G4:G6), but not the table heading.
Click in the Name box, to the left of the formula bar
Type a one-word name for the list -- RegList
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.
Select the employee names in column I (don't include the heading or regions)
Click in the Name box, to the left of the formula bar
Type a one-word name for the list -- EmpList.
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.
In cell A2, type a heading - Select a Region:"
Select cell B3, and format it with an outside border and light green fill.
With cell B3 selected, click the Data tab, then click Data Validation.
From the Allow drop-down list, choose List
In the Source box, type an equal sign and the list name: =RegList
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.
Select cell B3, and choose East from the drop down list.
Click in the Name box, to the left of the formula bar
Type a one-word name for cell B3 -- RegSel
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.
In cell K3, type the heading, ListNum
The table will expand automatically, to include the new column
In cell K4, type this formula, to number employees from the selected Region:
=IF(J4<>RegSel,"",SUM(MAX(K$3:K3),1))
Press Enter, and the formula will automatically fill down to the last row in the Employee table
(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))
First, the IF function compares the Region name in cell J4 to the region name in cell B3 (RegSel)
=IF(J4<>RegSel,
If the values are not equal (<>), the result is an empty string ("")
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.
In cell I1, type a label - # in Region - and right-align the cell.
In cell J1, enter this formula, to return the highest number from the ListNum column:
=MAX(tblEmp[ListNum])
Press Enter, and the formula will automatically fill down to the last row in the Employee table
(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:
Select cell J1
Click in the Name box, to the left of the formula bar
Type a one-word name for cell B3 -- RegNum
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:
In cells D3 and E3, type the headings - Num, Emp.
With one of the heading cells selected, on the Home tab, click Format as Table
Click on one of the Table Styles
Add a check mark for My table has headers, and click OK
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)
(optional) In cell D2, type a heading - Region Employees
Next, to name the table:
With a cell in the table selected,
click the Table Design tab at the top of Excel
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
The first IF function checks the cell above, to see if it has an empty string ("")
=IF(D4="",
If it does, the result is an empty string ("")
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)
Next, the INDEX function returns the employee name from that row in the Employee table
INDEX(tblEmp[Employee],
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
Select cell B3
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
Download Sample Files
NOTE: These zipped files are in xlsx format, and do not contain any macros.
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