Sorry for the vague title, but I myself cant summarize the problem in few words, so thanks for looking !

I have a few columns in a Table, with the first two columns playing a key part in getting the results.

Lets say the columns are -
A - Portfolio
B - Team
C - Project Cost
D - Project Manager

A sample table like the below -
Portfolio
Team
Cost
Project Manager
US Finance 100 A
US Implementation 100 B
US Dev 100 C
US Online Testing 100 D
US Offline Testing 100 E
ROW Implementation 100 R
ROW Dev 100 S
ROW Online Testing 100 T
ROW Offline Testing 100 U

What I need to do is the following -
  1. Choose the team's name (Col B) from a separate dropdown
  2. It should find which portfolios (Col A) the team is associated to.
  3. It lists all the rows, associated with the portfolio values found in the step above,e.g if I choose 'Dev' from a dropdown, 'Dev' is associated to the US and ROW portfolios, so all the rows belonging to US and ROW portfolios get listed down. Below results if 'Dev' is selected.


Team
(Selected from Drop Down) 		Portfolio
Team
 Cost
Project Manager
Dev US Finance 100 A
US Implementation 100 B
US Dev 100 C
US Online Testing 100 D
US Offline Testing 100 E
ROW Implementation 100 R
ROW Dev 100 S
ROW Online Testing 100 T
ROW Offline Testing 100 U

If however I chose the Team Finance, it is associated to only the US portfolio, so the results should be as below -

Team Portfolio
Team
 Cost
Project Manager
Finance US Finance 100 A
US Implementation 100 B
US Dev 100 C
US Online Testing 100 D
US Offline Testing 100 E

I tried to do it using a pivot, but it fetches only the particular row which belongs to the team, and not all teams associated to the portfolio.
I wish it would have struck me as it sounds simple, but unable to get it done today somehow .

Just to complicate the problem, I actually wanted to choose multiple values from the dropdown!!

Thanks in Advance !
Regards,
Ashutosh