How to do a dynamic table using SUMPRODUCT?

[gcefaloni]

Hi,

I'm trying to do a dynamic table that would change based on certain filters that I put. I would like to have a table that refers to a range in another worksheet in Excel and returns the top results given the parameters (filters) that I put in. So let's say, I would input filters that would request to pull bonds that have an maturity date between 2021 and 2025, have a BBB credit rating, have a spread above 100 above government bonds and the table would show the corresponding top results.

Here is an example of the filters:

Security selection dashboard - 2020-04-30.xlsx
	Q	R
1	Criteria	Parameters
2	Min Mat YR	2021
3	Max Mat YR	2025
4	Rating	BBB
5	Spread >	100
6	Sector 1	CORP
Dashboard

Here is an example of the table that would be outputting the results and the formula I'm trying right now (but that isn't working):

Security selection dashboard - 2020-04-30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID_CUSIP	Name	Ticker	Coupon Rate	Maturity Date	Sector 1	Sector 2	Rating	BB_5Y_DEFAULT_PROB	Ask Price	Ask Spread	Base Currency	COUNTRY	Spread/5 YR default rate
2
3
4
5
Dashboard

Cell Formulas
Range	Formula
A2:N5	A2	=IFERROR(INDEX(Rankings!$A$1:$Z$10000,SMALL(IF((YEAR(Rankings!$J$1:$J$10000)>=$Q$2)*(YEAR(Rankings!$J$1:$J$10000)<=$Q$3)*(Rankings!$K$1:$K$10000=$Q$4)*(Rankings!$D$1:$D$10000=$Q$5)*(Rankings!$E$1:$E$10000=$Q$6),ROW(Rankings!$O$1:$O$10000)),ROW(1:1)),MATCH(A$1,Rankings!$A$1:$Z$1,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Here is the range in another sheet that I would refer to:

Security selection dashboard - 2020-04-30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID_CUSIP	ID_BB_8_CHR	Ask Price	Ask Spread	SECTOR 1	SECTOR 2	Name	Ticker	Coupon Rate	Maturity Date	Rating	BB_5Y_DEFAULT_PROB	Base Currency	COUNTRY	Spread/5 YR default rate
2	70137TAD7	EK297293 Corp	100.5	-124.8	CORP	Energy	PARKLAND FUEL CORP	PKICN	5.5	5/28/2021	BB	2.79%	CAD	CANADA	-1
3	68323ADN1	JK424252 Corp	100.79	-48.71	PROV	ONT	ONTARIO (PROVINCE OF)	ONT	0	3/16/2021	AA-	1.19%	CAD	CANADA	0
4	563469TK1	EI148095 Corp	0	-40.8	PROV	Other	MANITOBA (PROVINCE OF)	MP	4.15	6/3/2020	A+	1.19%	CAD	CANADA	0
5	13509PEW9	EK929060 Corp	100.14	-36.9	FEDERAL	CANHOU	CANADA HOUSING TRUST	CANHOU	0	9/15/2020	AAA	1.19%	CAD	CANADA	0
6	13509PFE8	QJ828643 Corp	100.43	-30.72	FEDERAL	CANHOU	CANADA HOUSING TRUST	CANHOU	0	3/15/2021	AAA	1.19%	CAD	CANADA	0
7	68323AEB6	QZ993662 Corp	100.79	-28.54	PROV	ONT	ONTARIO (PROVINCE OF)	ONT	0	10/27/2021	AA-	1.19%	CAD	CANADA	0
Rankings

Cell Formulas
Range	Formula
A2:A7,E2:N7	A2	=IFERROR(INDEX('C:\Users\Gab\Dropbox\Nymbus Bonds\Databases\[Db - Canadian Bond Universe.xlsm]Aggregate'!$A$2:$AP$99998,MATCH(LEFT($B2,LEN($B2)-5),'C:\Users\Gab\Dropbox\Nymbus Bonds\Databases\[Db - Canadian Bond Universe.xlsm]Aggregate'!$B$2:$B$14998,0),MATCH(A$1,'C:\Users\Gab\Dropbox\Nymbus Bonds\Databases\[Db - Canadian Bond Universe.xlsm]Aggregate'!$2:$2,0)),"")
B2	B2	=IF('RUNZ Export'!A1219<>0,'RUNZ Export'!A1219,"")
C2:D7	C2	=IFERROR(INDEX('RUNZ Export'!$A:$X,MATCH(INDIRECT("B"&ROW()),'RUNZ Export'!$A:$A,0),MATCH(Rankings!C$1,'RUNZ Export'!$1:$1,0)),"")
O2:O7	O2	=IFERROR(IFERROR(((D2^3)/100000000)/L2,((D2^3)/1000000)/INDEX(Table1[#All],MATCH(INDIRECT("K"&ROW()),Table1[[#All],[CORP Equivalent Rating]],0),MATCH("Default Risk %",Table1[#Headers],0))),"")
B3	B3	=IF('RUNZ Export'!A383<>0,'RUNZ Export'!A383,"")
B4	B4	=IF('RUNZ Export'!A382<>0,'RUNZ Export'!A382,"")
B5	B5	=IF('RUNZ Export'!A381<>0,'RUNZ Export'!A381,"")
B6	B6	=IF('RUNZ Export'!A380<>0,'RUNZ Export'!A380,"")
B7	B7	=IF('RUNZ Export'!A379<>0,'RUNZ Export'!A379,"")

I think the problem I'm having is related to the fact that the above range I'm referring to is a filtered range with TONS of blanks and errors in it. I would probably need to find a way to filter out the errors and the blanks and return the clean results in order for my formula to work. Here is the formula I was playing around with for now: =IFERROR(INDEX(Rankings!$A$1:$Z$10000,SMALL(IF((YEAR(Rankings!$J$1:$J$10000)>=$Q$2)*(YEAR(Rankings!$J$1:$J$10000)<=$Q$3)*(Rankings!$K$1:$K$10000=$Q$4)*(Rankings!$D$1:$D$10000=$Q$5)*(Rankings!$E$1:$E$10000=$Q$6),ROW(Rankings!$O$1:$O$10000)),ROW(1:1)),MATCH(B$1,Rankings!$A$1:$Z$1,0)),"")

Any help would be extremely appreciated to help me figure out why it isn't working and/or if I am right and it is linked to a filtered range with blanks & errors; how would you amend my formula to ignore those blanks and return the top results for my search?

Thank you in advance!

 

[jasonb75]

Main problem that I can see is that your formula is referring to the criteria description ($Q$2 etc) where it should be referring to the parameter ($R$2 etc).

It might need other changes, for the filter, blanks, errors, etc. Instead of using
SMALL(IF((range1 = criteria1)*(range2 = criteria2),ROW(range)),ROW(1:1))
try
AGGREGATE(15,6,ROW(range)/(range1=criteria1)/(range2=criteria2),ROWS(A$2:A2))

Note that ROW(range) is moved to the front of the array. There is no IF() function. Division is used instead of multiplication. You don't need to array confirm this method.
Finally, the last part uses ROWS(A$2:A2) instead of ROW(1:1) with the range referring to the top left cell in the range holding the formulas with the single $ symbol to make it dynamic.

That should help with any formula errors in the data. You might need additional changes to work with filtered data or blanks not excluded by the criteria in R2:R6

 

[gcefaloni]

Main problem that I can see is that your formula is referring to the criteria description ($Q$2 etc) where it should be referring to the parameter ($R$2 etc).

It might need other changes, for the filter, blanks, errors, etc. Instead of using
SMALL(IF((range1 = criteria1)*(range2 = criteria2),ROW(range)),ROW(1:1))
try
AGGREGATE(15,6,ROW(range)/(range1=criteria1)/(range2=criteria2),ROWS(A$2:A2))

Note that ROW(range) is moved to the front of the array. There is no IF() function. Division is used instead of multiplication. You don't need to array confirm this method.
Finally, the last part uses ROWS(A$2:A2) instead of ROW(1:1) with the range referring to the top left cell in the range holding the formulas with the single $ symbol to make it dynamic.

That should help with any formula errors in the data. You might need additional changes to work with filtered data or blanks not excluded by the criteria in R2:R6

The Q2 vs R2 reference was a non-issue because I deleted a column after posting the parameters table above. But it was referencing to the proper cell.
However, your suggestion of using aggregate (which is a function im a bit less familiar with) rather than SMALL seems to have settled the issue that I was experiencing with the blanks and errors. I'm not sure I understand the difference yet to be honest even though I would love to. Either way, thank you so much for your suggestion! If you have time to perhaps explain to me why aggregate works and why small did not, it would be great.

One way or another, your help was extremely appreciated. Thank you again!

 

[jasonb75]

Aggregate is able to ignore errors while small would need additional functions to filter them out.

In addition to this the division method creates errors for the rows that don't meet the criteria (meaning that they are ignored by aggregate) where the multiplication of small(if creates zero values for the same rows. Being smaller than the valid row numbers, small passes the zero's to index first, which means that it is returning the entire column instead of a single row. Because your formula is only meant to return a single cell result index returns an error which iferror is then blanking out.

 

[gcefaloni]

While you understand the issue, can I you 2 quick follow up questions?
1- In the ratings parameter: there are essentially 3 options; one rating with a +, with no sign, and with a - (e.g. BBB+, BBB, BBB-, or AA+, AA, AA-, or A+,A,A-). How would I go about grouping all ratings of one type (BBB, AA, A) regardless of the sign into one group so that when I put BBB in R4 in the parameters table above, it returns bonds of all 3 subcategories BBB+, BBB, and BBB- instead of just BBB.

2- How do I enter a wildcard in the parameters table in order to indicate that anything can be returned in this field. Basically, without changing the formulas in the output table, I could put in the equivalent of "*" in R4 or R5 and then it would return any credit rating with any spread without filtering for those parameters because I entered a wildcard for the parameters in question?

Thanks again!

 

[jasonb75]

The first part is going to make the formula excessively complicated, using wildcards would allow false results (A with a wildcard would return AA as well). The only reliable way would be to use

/(((Range1=Criteria1)+(Range1=(Criteria1&"-"))+(Range1=(Criteria1&"+")))>0)/

For the rating criteria section of the array.

The formula is not suitable for wildcards in the criteria cells, they would have to go into the formula, again increasing the complexity of the array. Previously I've advised people to use a criteria of "All" for this purpose, with an array of

/IF(Criteria="All",1,Range1=Criteria1)/

Adding IF into the formula means that it will also need to be array confirmed. The TRUE result of 1 when criteria ="All" means division by 1, which is no change to the array so all rows are accepted, nothing is filtered out.

 

[gcefaloni]

The first part is going to make the formula excessively complicated, using wildcards would allow false results (A with a wildcard would return AA as well). The only reliable way would be to use

/(((Range1=Criteria1)+(Range1=(Criteria1&"-"))+(Range1=(Criteria1&"+")))>0)/

For the rating criteria section of the array.

The formula is not suitable for wildcards in the criteria cells, they would have to go into the formula, again increasing the complexity of the array. Previously I've advised people to use a criteria of "All" for this purpose, with an array of

/IF(Criteria="All",1,Range1=Criteria1)/

Adding IF into the formula means that it will also need to be array confirmed. The TRUE result of 1 when criteria ="All" means division by 1, which is no change to the array so all rows are accepted, nothing is filtered out.

This works just perfectly. Thank you SO much!