Assigning a list to a category

Hello

I have an excel sheet much like the one below. Basically, there are two different teams that share the same sub-categories but different tasks. When I do a sort though, say on the person's name, the category and sub-category's are not linked to the person. The objective, person and task will always be linked on a line with each other but how can I link that line to the sub-category and then that sub-category to the category?

EXCEL.PNG
excel2.PNG

I just want it related so that when I do a filter, like the photo above, the team name (Category) show ups so it is a quick snapshot of what this person needs to do for each team.

Using helper columns to extract:

H3:
=LOOKUP(2,1/(B$2:B3<>""),B$2:B3)

Drag down and accross

Attachment is invalid.

Sorry! This should work:

The team name is still not showing

excel3.PNG

The way I was hoping for it to work is:

One Team > Categories > objectives > Tasks

So One team will have many categories and in those categories, there will be many objectives and in those objectives, I have many task assigned to a person with a due date
When I sort this, I need it to show the: Team Name, Category and Objective. This way I can sort by person or do date and still see the top tier information associated with that task

Could you attach a worksheet with your manual outcome?

Works with helper table in the right. If you wanted to work with the left the table, just copy the right one then paste value back to table in the left.