Dynamic dataset building in excel

Question

I have a large dataset that holds constituent information for an investment index. I want to take the large set of data and create a new dataset/table/sheet that matches certain criteria. Typically, I would just filter the index constituent dataset and copy and paste but I need it to be dynamic.

Take the mini 'large dataset' below for example...

Since the index holdings/CUSIPs can change daily, I want to dynamically create a table based on this table that only includes CUSIPs that are in the Tech sector, have a maturity of 0-3Y, and a credit rating of BB. How would I go about doing this dynamically?

I cannot use VBA because the future users of this workbook are not familiar with coding. I tried using a pivot table to achieve this but it didn't work out (although, I'm terrible with pivot tables so this could very well be the solution). Any help is much appreciated!

Answer 1

You could try using FILTER() function here:

---

=LET(x, FILTER(A:E,(B:B="Tech")*(C:C="0-3Y")*(D:D="BB"),""), IF(x=0,"",x))

---

Also, instead of using the whole ranges, which will slow down the excel working functionality suggest you to convert the range into a Structured Reference and then use the following formula as is:

---

=LET(
     x, FILTER(IndexConst,
              (IndexConst[SECTOR]="Tech")*
              (IndexConst[MATURITY]="0-3Y")*
              (IndexConst[CREDIT RATING]="BB"),
         ""), 
     IF(x=0,"",x))

---

Note: I have named the table as IndexConst you may need to change as per your suit.