Return Multiple Values for a Lookup Formula in Excel with FILTER and UNIQUE

Bottom Line: Learn how to use the FILTER and UNIQUE functions to return multiple values for a lookup based on a condition.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.

The file that I work within the video can be found below.  You can also download the “Follow Along” version if you'd like to practice building out the report.

Compatibility: This file uses the new Dynamic Array Functions that are only available on the latest version of Office 365. This includes both the desktop and web app versions of Excel.

I'm planning to post a bonus episode in this series that covers how to make the dashboard with older versions of Excel using pivot tables instead.

Building an Attendance Dashboard

This post is part 3 of a six-part series explaining how to build an interactive dashboard for attendance.  This attendance report was an entry for the Excel Hash competition.  I recommend you check out the first two posts here:

Dynamic Array Functions

This post will feature two of the new Dynamic Array Functions available through Office 365.  Dynamic Array Functions allow for multiple values to be to a range.  This is referred to as the Spill Range because the results spill over into blank cells beneath the cell that contains the formula.

This new functionality is really helpful, as you will see. The two Dynamic Array Functions we will be looking at in today's post are UNIQUE and FILTER

The UNIQUE Function

The UNIQUE function evaluates a range of data and pulls out all the duplicates, returning a list of unique entries from that range.

The function has three arguments, but only the first one is required. It is the Range/Array to evaluate.

In this example we want to create a list of unique employee names from our data table.  This will be used for the summary report of total duration per employee.

The data table contains a list of time entries with multiple rows per employee.  So in order to return a list of unique values we can use the following formula.

=UNIQUE(tblData[Employee])

The result is a list of all employees in the data set.

The FILTER Function

Now we want to filter our list of employees by department.  The FILTER function is great for this task.  It is essentially like a lookup that returns multiple results.  We want to lookup the a specific department in the department column and return all the matching employee names (people in the department).

The FILTER function has three arguments. The first is the array that we want to return. As before, this is the Employee column on the Data sheet.

The second argument is Include, and it is the filter criteria that we want to specify. In our case, that criteria is the department. So our argument will essentially tell Excel to examine the list of departments on the Data tab and see which ones match the department that's in our dashboard drop-down list.

Dashboard!B5=tblData[Dept]

The third argument [is_empty] indicates what to do if no matches are found.  FILTER has built-in error handling so the formula will not return an error if nothing matches the filter criteria.  I used the word “Empty”, but you can use a more descriptive phrase if other people are using your file.

Our final formula, then, looks like this:

=FILTER(tblData[Employee],Dashboard!B5=tblData[Dept],”Empty”)

The formula returns a list of all the employees in the specified department.  As you can see in the image above, there are duplicates that we need to eliminate.

Combining the Functions

Because the FILTER function returns duplicate entries, we can combine it with UNIQUE to pull those out:

=UNIQUE(FILTER(tblData[Employee],Dashboard!B5=tblData[Dept],”Empty”))

Filtering for All Departments

The last problem to overcome is filtering by all departments. Because “All Depts” is not listed for any employee specifically, the filter function cannot find it, and the word “Empty” is returned when that option is selected on the dashboard.

To take care of this, we can wrap everything we have done so far (represented by XXX below) in an IF function.

=IF(Dashboard!B5=Calc!J2, UNIQUE(tblData[Employee]),XXX)

Because we have already pulled out a unique list of employees, an alternative would be to reference that spill range, which is B2#.

=IF(Dashboard!B5=Calc!J2, B2#,XXX)

Conclusion

Using these Dynamic Array Functions is a really neat way to quickly return multiple results to multiple cells. They are a great alternative to VLOOKUP and allow you to create a dynamic range based on a certain criteria.

UNIQUE removes duplicates in a list, returning a clean list of unique values.  FILTER returns multiple results based on lookup criteria with a simple straightforward formula.

• Jonas Tyrone Lobaton says:

Become an Excelchat Expert!

Excelchat is a website that helps professionals and even students with their Excel and Google Sheets related concerns.

You will be one of the Excelchat Expert who will help our clients with their Excel or Google Sheets related concerns.
Your support coverage is only limited to all spreadsheet issues such as formulas, charts and Pivot tables (application-related issues, VBA, and Google Scripts are not included).
We have the one issue per transaction policy so you don’t get bombarded by tons of problems.

Earn up to \$4 per 20-minute transaction.
No minimum working hours and you get to choose your own schedule.
You get to choose which chat to answer.

Requirements
Any computer or laptop will work as long it doesn’t lag and it has a good internet connection.
Good communications skills.

Assessments
Orientation about Excelchat and training on using the platform and your role.
39-item quiz related to Excel that you have to take in 70 minutes.
Mock chat and mentoring with an an Excelchat mentor. You’ll have your first practice chat with a mentor (60 minutes).

Getting Started
1. Click the link below and create an account with Excelchat.
2. Take the three-part training and assessment phase.
3. Once you passed, you can start earning while learning.

https://tinyurl.com/excelchat

• Shobhnath says:

Thanks you sir your providing such benefit that for me aswesome

Sir pls help me

how to search in excel missing sequence number. for example
HR/19-20/001
HR/19-20/002
HR/19-20/004

• Great question! I’ll add this to our list for future posts. For this specific example you could use a formula like the following to evaluate the right 3 numbers of the value in the current row, compared to the value in the cell above.

This formula would go in row 2 and be copied down. The values would be in column A.

=IF(VALUE(RIGHT(A1,3))<>VALUE(RIGHT(A2,3)-1),”Missing”,””)

I hope that helps get you started. Thanks again and have a nice weekend!

• Baikunth pal says:

Thanks sir you providing such benefit that for me aswesome

Generic filters
Exact matches only
Filter by Custom Post Type