Overview of Need
Our first objective is to generate a list of Employee Names and Positions for any employee whose salary is greater than an amount defined in cell J2.
We want to build these results in a dynamic way. If the salary threshold in cell J2 changes, we want our list to automatically update.
NOTE: For demonstration purposes, we’ve located the data and the lookup results next to one another on the same sheet. This makes for easier testing. There is no requirement for this arrangement. You can place the data and lookup results on separate sheets or even in separate files.
Preparing the Data
We want to ensure that newly added records to the table are seen by the lookup function, so we will turn the list of information into a proper Excel Table. This will also make for easier to read formulas.
- Click somewhere in the range of data and press CTRL-T.
- In the Create Table dialog box, ensure the selected data range is correct (in this case, =$A$2:$F$68) and the “My table has headers” option is selected, then click OK.
- Consider removing the applied colors by setting the table style to None (upper-left color option).
- Name the table “TSal” by selecting Table Design (tab) -> Properties (group) -> Table Name.
Creating the Single-Formula Solution
To achieve this wonderous feat of legerdemain, we will take advantage of one of the new Dynamic Array functions available to Office 365 users (sorry everyone else; I guess it’s time to upgrade) called FILTER.
If you’re not familiar with the FILTER function, click the link below for a great post and video on its use.
Excel’s New Dynamic Array FILTER Function
We’ll build this solution in stages, so we don’t have to think about too much at any single moment.
For our Range argument, we want to select the Name (column B) and Position (column E) columns in the data, but FILTER only allows for a single range. We will have to select the columns that lie between Name and Position but ignore them in the result.
Begin by creating the beginning of the FILTER function in cell I5.
=FILTER(TSal[[Name]:[Position]],For our Include argument, we want to discover any row with a salary greater than what is defined in cell J2 (100,000).
=FILTER(TSal[[Name]:[Position]], Tsal[Salary] > J2,We could add a third optional argument that displays a message if no records are discovered…
=FILTER(TSal[[Name]:[Position]], Tsal[Salary] > J2, “No Records Found”)… but we are confident we will always have at least one record returned, so we will leave that argument out of our formula for simplicity’s sake.
=FILTER(TSal[[Name]:[Position]], Tsal[Salary] > J2)The formula returns the following results in a SPILL range.
The unwanted side-effect of this result is we are including the Start Date and Birth Date columns in our filtered result.
The results aren’t formatted as dates, so they look a bit strange; but trust me, they are dates. These are the number-equivalent versions of dates.
The next step is to remove the unwanted columns from the result.
Applying a Filter to a Filter
To remove the unwanted columns from the previous result, we will run the results through another filter function to kick out the unwanted columns.
We start by updating the formula by placing the entire existing FILTER formula within another FILTER function. The original FILTER function will be the Array argument of the additional FILTER function.
=FILTER(FILTER(TSal[[Name]:[Position]], Tsal[Salary] > J2),The Include argument will use array syntax to select the 1st and 4th columns, in their entirety, from the original FILTER results.
=FILTER(FILTER(TSal[[Name]:[Position]], Tsal[Salary] > J2), {1, 0, 0, 1})Array Syntax
In Excel, 1 is treated as TRUE while 0 (zero) is treated as FALSE.
Excel will apply a 1 (TRUE) to every item in the 1st column of the Array argument, a 0 (False) to every item in the 2nd and 3rd columns, and a 1 to every item in the 4th column.
This is a sneaky trick to get Excel to flag items you want to keep versus items you want to ignore.
The results are as follows.
Sorting the Results
As an extra touch, we can sort the results by Name in ascending order by wrapping the FILTER/FILTER formula in a SORT function.
=SORT(FILTER(FILTER(TSal[[Name]:[Position]], Tsal[Salary] > J2), {1, 0, 0, 1}) )
If you want to sort by Position instead of by Name, add a 2 to the [Sort_Index] argument to instruct SORT to filter by the 2nd column instead of the default 1st column.
=SORT(FILTER(FILTER(TSal[[Name]:[Position]], Tsal[Salary] > J2), {1, 0, 0, 1}), 2)
Creating a Unique List of Non-Adjacent Columns
Suppose we need to generate a list of each department’s positions and we don’t want any duplicates in the result.
We can integrate the UNIQUE function while at the same time using the 1/0 trick to produce the result.
- Since we want all the columns of the original table except the Salary column, we will redefine our Array argument to go from Department (column A) to Position (column E).
=FILTER(TSal[[Department]:[Position]],- We’ll use the 1/0 trick to remove the unwanted columns.
=FILTER(TSal[[Department]:[Position]], {1, 0, 0, 0, 1})- Next, we’ll reduce the list to only one of each Department/Position combination; in other words, remove the duplicates.
=UNIQUE(FILTER(TSal[[Department]:[Position]], {1, 0, 0, 0, 1}) )- Lastly, we’ll sort the list in ascending order by Department.
=SORT(UNIQUE(FILTER(TSal[[Department]:[Position]], {1, 0, 0, 0, 1}) ) )If you wanted to perform a primary sort by Department and a secondary sort by Position, you could add instructions to the [sort_index] argument to sort by the 1st column, then by the 2nd column.
=SORT(UNIQUE(FILTER(TSal[[Department]:[Position]], {1, 0, 0, 0, 1}) ), {1, 2} )
Testing the Dynamic Nature of the Solution
To ensure our result will operate properly under real-world conditions, perform the following tests:
- Change the earnings threshold (cell J2) to different values and see if the Name/Position table updates.
- Add new records to the table and see if those new records appear in the Name/Position table (assuming they have salaries over the value defined in cell J2).
- Change existing records or add new records that have departments or positions that did not originally exist and see if the unique list of Departments/Positions
Super-Amazing “Hidden” Sort Trick
Returning to the list of Names and Positions we generated earlier; suppose we wanted to sort the list in descending order by Salary.
Since there’s no Salary information in the result, this seems like an impossible task.
A very clever trick is to rearrange the nesting order of the functions to apply actions in a different sequence.
Take, for example, the following updated formula:
=FILTER(SORT(FILTER(TSal[[Name]:[Salary]], TSal[Salary] > J2), 5, -1), {1, 0, 0, 1, 0})Let’s break it down.
We begin by selecting the columns of data from Name to Salary.
=FILTER(TSal[[Name]:[Salary]],Next, we filter the rows to only include those with salaries greater than the value defined in cell J2.
=FILTER(TSal[[Name]:[Salary]], TSal[Salary] > J2)Here’s the magic ingredient: we take the results as they stand now, a table that includes salaries, and we apply a SORT function to the 5th column (Salary) and apply a descending sort (-1).
=SORT(FILTER(TSal[[Name]:[Salary]], TSal[Salary] > J2), 5, -1)With the newly sorted table, we use our previously discovered 1/0 trick to keep the 1st and 4th columns (Name & Position) while filtering out the remaining columns.
=FILTER(SORT(FILTER(TSal[[Name]:[Salary]], TSal[Salary] > J2), 5, -1), {1, 0, 0, 1, 0})
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
