Final Friday Fix – June 2021
25 June 2021
On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”. Challenge your office colleagues to see who can solve the puzzle quickest. There are no prizes at this stage: you are playing for bragging rights only!
We’ve talked at long length about Dynamic Arrays and how the value that they add to Excel and the solutions that we can build with them. But sometimes, as we keep pushing to solve ever increasingly difficult problems, we end up hitting some fairly hard limits.
In this instance, we have a list of employees where we know what division each person works in. We also have a list of capabilities that each employee brings to the team. If we select an employee, we’d like to know what their whole team brings to the table.
So to do this, we naturally follow some basic steps – what’s your employee’s name, and what division are they from? So simple, so INDEX(MATCH()).
=INDEX(Table1[Division],MATCH(Employee_Selection,Table1[Employee],0))
Then, we might even be able to find a list of all the employees that work in that division. That’s what FILTER is for, right?
=FILTER(Table1[Employee],Table1[Division]=Division_Selected)
Then, of course, we just need to filter our capability list on the list of employees. Simple, right?
=FILTER(Table2[Capabilities],Table2[Employee]=Division_Employees)
Wait, what?
The Challenge
This month’s challenge is very straightforward. Can you come up with a formula to give us a list of capabilities that each person in the team can provide?
Sounds easy? Then why not have a go? We’ll publish one solution in Monday’s blog. Have a great weekend in the meantime!
