How to filter the data in one worksheet based on the filtered results in another

1
votes

I have 2 different worksheets, one with a series of about 2000 mutual funds and corresponding information for each fund (FundName, sector, cost in %, etc.) the other one has historical monthly returns for each fund displayed in 3 different columns (A = FundName, B = Last Day of the Month, C = Monthly Return for the given Month).

What I am doing is filtering the mutual funds in the first worksheet to keep only the ones that cost under a certain amount, are in a specific sector so on and so forth. Then I would like to filter the "monthly returns" worksheet to only display the returns for the funds that have been filtered in the "FundInfo" worksheet.

I'm thinking a good way of doing that would be to match the FundName field that is present in both worksheets so that it only returns for the funds that are present in the filtered FundInfo worksheet also show up on the monthly returns worksheet but I'm not sure how to do that. I've tried to play around with the advanced filters but without much success.

1
excelfilterexcel-formulaworksheet-functionadvanced-filter

1 Answers

1
votes

My solution to this problem would be to add a column to the Fund Info worksheet that determines if the fund meets your criteria. For example, if you are looking for a fund that invests in financials or energy, and costs between 10 and 50 bps:

=AND(OR($B1="Financials",$B1="Energy"),$C1<0.005,$C1>0.001)

This will return TRUE or FALSE for each fund, assuming that:

  • Column B = Sector
  • Column C = Cost

Then, in your Fund Returns worksheet, add a column that looks up the Boolean value from Fund Info, using VLOOKUP and keying off of the fund name.

Lastly, you can filter your Fund Returns sheet where this value is TRUE.