Index Match results = WTF?

[gcefaloni]

Hi,

I've been using a combination of Index Match and Aggregate for other similar functions that have been working very well. However, this specific one seems to not return the proper output for the match function.
This is for use in a dynamic table that returns ranked results from a list of parameters:

Here are the parameters:

Security selection dashboard - 2020-04-30.xlsx
	R	S
31	Criteria	Parameters
32	Min Mat YR	2020
33	Max Mat YR	2040
34	Rating	A
35	Spread >	All
36	Sector 1	All
37	Sector 2	All
38	Coupon Type	All
39
Dashboard

If you look at the formula in the cell under the header "Name", it should return something like "JOHN DEERE CANADA FND IN" (column #25 in match array) but instead it returns "CAD" which isn't the column that it should return (#23 in match array).

Security selection dashboard - 2020-04-30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
31	ID_CUSIP	Name	Ticker	Coupon Rate	Maturity Date	Sector 1	Sector 2	Rating	RSK_BB_ISSUER_DEFAULT	BB_5Y_DEFAULT_PROB	Yield	Ask Spread	Base Currency	COUNTRY	CPN_TYP
32	013051DW4	CAD	2		CAD	CAD	CAD	CAD			2
Dashboard

Cell Formulas
Range	Formula
A32:H32,K32:O32	A32	=IFERROR(INDEX('[CORE PORT.xlsm]PORTF'!$A$8:$AA$1000, AGGREGATE(15,6,ROW('[CORE PORT.xlsm]PORTF'!$A$9:$A$1000)/ IF($S$32="All",1,(YEAR('[CORE PORT.xlsm]PORTF'!$E$9:$E$1000)>=$S$32))/ IF($S$33="All",1,(YEAR('[CORE PORT.xlsm]PORTF'!$E$9:$E$1000)<=$S$33))/ IF($S$34="All",1,(('[CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34)+('[CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$4&"-")+('[CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$4&"+")))/ IF($S$35="All",1,('[CORE PORT.xlsm]PORTF'!$G$9:$G$1000>$S$35))/ IF($S$36="All",1,('[CORE PORT.xlsm]PORTF'!$Q$9:$Q$1000=$S$36))/ IF($S$37="All",1,('[CORE PORT.xlsm]PORTF'!$R$9:$R$1000=$S$37))/ IF($S$38="All",1,('[CORE PORT.xlsm]PORTF'!$V$9:$V$1000=$S$38)), ROWS(A$32:A32)),MATCH(A$31,'[CORE PORT.xlsm]PORTF'!$A$8:$AA$8)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

This is part of the table that it refers to:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
8	CUSIP	Pos MM$	TICKER	COUPON RATE	MATURITY DATE	PX_LAST	YIELD	YAS_YLD_SPREAD	G-Spread Percentile between MIN/MAX Hist. Spread.	YAS_BNCHMRK_SECURITY_DES	DUR_ADJ_MID	PV01	$PV01	INT_ACC	Amount MM$	Inc Accr	SECTOR 1	SECTOR 2	Rating	NXT CPN	COUNTRY	CPN_TYP	Base Currency	Settle	NAME	Avg Cost	Coupon Frequency
9	25675TAD9	1.000	DOLCN	2.34%	22-Jul-21												CORP	Consumer, Cyclical	BBB	-	CANADA	FIXED	CAD	3-Sep-19	DOLLARAMA INC	100.370	2
10	47787ZBW1	0.444	DE	2.70%	12-Oct-21												CORP	Industrial	A	-	CANADA	FIXED	CAD	5-Feb-19	JOHN DEERE CANADA FND IN	100.165	2
11	154728AN9	1.100	CTLONE	2.60%	7-Nov-22												CORP	Banks	A-	-	CANADA	FIXED	CAD	5-Feb-19	CENTRAL 1 CREDIT UNION	98.200	2
12	190330AC4	1.000	COCAPS	5.00%	3-May-23												CORP	Diversified Finan Serv	BBB	-	CANADA	VARIABLE	CAD	5-Feb-19	COAST CAPITAL SV CRE UN	102.938	2
13	013051DM6	0.200	ALTA	3.10%	1-Jun-24												PROV	ALTA	AA-	-	CANADA	FIXED	CAD	13-Mar-20	PROVINCE OF ALBERTA	107.762	2
14	80310ZAC3	0.600	SAPCN	2.83%	21-Nov-23												CORP	Consumer, Non-cyclical	BBB+	-	CANADA	FIXED	CAD	5-Feb-19	SAPUTO INC	98.788	2
15	06415ELY9	1.000	BNS	3.89%	18-Jan-24												CORP	Banks	BBB+	-	CANADA	VARIABLE	CAD	5-Feb-19	BANK OF NOVA SCOTIA	101.838	2
16	07813ZBZ6	1.000	BCECN	3.35%	12-Mar-25												CORP	Communications	BBB+	-	CANADA	FIXED	CAD	5-Feb-19	BELL CANADA	100.650	2
17	013051DW4	-	ALTA	2.55%	1-Jun-27												PROV	ALTA	AA-	-	CANADA	FIXED	CAD	13-Mar-20	PROVINCE OF ALBERTA	106.334	2
Sheet1

Any clue on what is going here? Why is it returning the wrong column in the index match function?

Any help will be INCREDIBLY appreciated.

Thank you!

P.S. Could it be related to the fact that the last table that the index match refers to is not in the same workbook and is located somewhere else on the computer or that has nothing to do with it?

 

[KRice]

I should have emphasized something mentioned in an earlier post: I think all of the ranges in the AGGREGATE function that refer to columns of data in PORTF should run from 8:1000 rather than 9:1000 to maintain a one-to-one correspondence with the range used by the INDEX function (i.e., '[CORE PORT.xlsm]PORTF'!$A$8:$AA$1000).

 

[gcefaloni]

Filtering for Rating is controlled by the content of S34 on the Dashboard sheet, and it appears that you either enter "All" or a specific rating in S34. I suspect the issue is with that particular component inside the AGGREGATE function:
IF($S$34="All",1,(('[CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34)+('[CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$4&"-")+('[CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$4&"+")))/

What is in S4 on the Dashboard sheet?...or do you want the two additional terms inside this IF statement to refer to $S$34&"-" and $S$34&"+"
...assuming these are ratings similar to bond ratings where if you filter on A, you also want A- and A+?

Yea, I did change that to all S34 but it still doesn't return the proper row. This is the formula I have now:

Security selection dashboard - 2020-04-30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
31	CUSIP	Name	Ticker	Coupon Rate	Maturity Date	Sector 1	Sector 2	Rating	Pos MM$	Inc Accr	Yield	Ask Spread	Base Currency	COUNTRY	CPN_TYP
32	013051DW4	PROVINCE OF ALBERTA	ALTA	2.55%	2027-06-01	PROV	ALTA	AA-	0	0	1.40%		CAD	CANADA	FIXED
33	83179XAH1	SMARTCENTRES REIT	SRUUCN	3.83%	2027-12-21	CORP	Real Estate	BBB+	0.35	0.371641123	3.17%		CAD	CANADA	FIXED
Dashboard

Cell Formulas
Range	Formula
A32:O32	A32	=IFERROR(INDEX('CORE PORT.xlsm]PORTF'!$A$8:$AA$1000, AGGREGATE(15,6,ROW('CORE PORT.xlsm]PORTF'!$A$9:$A$1000)/ IF($S$32="All",1,(YEAR('CORE PORT.xlsm]PORTF'!$E$9:$E$1000)>=$S$32))/ IF($S$33="All",1,(YEAR('CORE PORT.xlsm]PORTF'!$E$9:$E$1000)<=$S$33))/ IF($S$34="All",1,(('CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34)+('CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34&"-")+('CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34&"+")))/ IF($S$35="All",1,('CORE PORT.xlsm]PORTF'!$G$9:$G$1000>$S$35))/ IF($S$36="All",1,('CORE PORT.xlsm]PORTF'!$Q$9:$Q$1000=$S$36))/ IF($S$37="All",1,('CORE PORT.xlsm]PORTF'!$R$9:$R$1000=$S$37))/ IF($S$38="All",1,('CORE PORT.xlsm]PORTF'!$V$9:$V$1000=$S$38)), ROWS(A32:A$32)),MATCH(A$31,'CORE PORT.xlsm]PORTF'!$A$8:$AA$8,0)),"")
A33:O33	A33	=IFERROR(INDEX('CORE PORT.xlsm]PORTF'!$A$8:$AA$1000, AGGREGATE(15,6,ROW('CORE PORT.xlsm]PORTF'!$A$9:$A$1000)/ IF($S$32="All",1,(YEAR('CORE PORT.xlsm]PORTF'!$E$9:$E$1000)>=$S$32))/ IF($S$33="All",1,(YEAR('CORE PORT.xlsm]PORTF'!$E$9:$E$1000)<=$S$33))/ IF($S$34="All",1,(('CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34)+('CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34&"-")+('CORE PORT.xlsm]PORTF'!$S$9:$S$1000=$S$34&"+")))/ IF($S$35="All",1,('CORE PORT.xlsm]PORTF'!$G$9:$G$1000>$S$35))/ IF($S$36="All",1,('CORE PORT.xlsm]PORTF'!$Q$9:$Q$1000=$S$36))/ IF($S$37="All",1,('CORE PORT.xlsm]PORTF'!$R$9:$R$1000=$S$37))/ IF($S$38="All",1,('CORE PORT.xlsm]PORTF'!$V$9:$V$1000=$S$38)), ROWS(A$32:A33)),MATCH(A$31,'CORE PORT.xlsm]PORTF'!$A$8:$AA$8,0)),"")

Security selection dashboard - 2020-04-30.xlsx
	R	S
31	Criteria	Parameters
32	Min Mat YR	2020
33	Max Mat YR	2023
34	Rating	A
35	Spread >	All
36	Sector 1	All
37	Sector 2	All
38	Coupon Type	All
Dashboard

 

[KRice]

Four things:
1) Change all of your references from 9:1000 to 8:1000
2) Go to this part of your A32 formula: ROWS(A32:A$32)) ...I think you want ROWS(A$32:A32)) so that the initial row (row 32) returns a "1" for the 1st smallest row value that matches criteria, then as the formula is pulled down that component of the formula will become ROWS(A$32:A33)), returning a 2 for the 2nd smallest row value, etc. I'm assuming you've pulled this set of formulas down sufficiently far to capture all of the matches?
3) If the formula is constructed correctly, you shouldn't have two different formulas in A32 and A33...instead, what is entered in A32 will automatically modify cell references when it is pulled down. I see the A32 vs A$32 issue is probably causing this.
4) I just noticed something else...in your AGGREGATE function, you have as the numerator ROW('CORE PORT.xlsm]PORTF'!$A$9:$A$1000)/
Realize that this will return values of 9, 10, 11, etc., and my suggested change from 9:1000 to 8:1000 in #1 above will cause this array to be 8, 9, 10, 11, etc. You really want 1, 2, 3, 4 ,etc so that these become index positions within the INDEX('CORE PORT.xlsm]PORTF'!$A$8:$AA$1000 array. So to achieve that, you want (ROW('CORE PORT.xlsm]PORTF'!$A$8:$A$1000) - ROW('CORE PORT.xlsm]PORTF'!$A$7)) /
Note that this is all wrapped inside parentheses.

 

[gcefaloni]

Three things:
1) Change all of your references from 9:1000 to 8:1000
2) Go to this part of your A32 formula: ROWS(A32:A$32)) ...I think you want ROWS(A$32:A32)) so that the initial row (row 32) returns a "1" for the 1st smallest row value that matches criteria, then as the formula is pulled down that component of the formula will become ROWS(A$32:A33)), returning a 2 for the 2nd smallest row value, etc. I'm assuming you've pulled this set of formulas down sufficiently far to capture all of the matches?
3) If the formula is constructed correctly, you shouldn't have two different formulas in A32 and A33...instead, what is entered in A32 will automatically modify cell references when it is pulled down. I see the A32 vs A$32 issue is probably causing this.

1- I did, doesn't change anything as you can see below.
2- Made the change, also doesn't seem to have any effect.
3- I'm not sure, I just drag down row 32 to 33 so indeed the formulas should be the same.

Security selection dashboard - 2020-04-30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
31	CUSIP	Name	Ticker	Coupon Rate	Maturity Date	Sector 1	Sector 2	Rating	Pos MM$	Inc Accr	Yield	Ask Spread	Base Currency	COUNTRY	CPN_TYP
32	013051DW4	PROVINCE OF ALBERTA	ALTA	0.0255	46539	PROV	ALTA	AA-	0	0	0.01401		CAD	CANADA	FIXED
33	83179XAH1	SMARTCENTRES REIT	SRUUCN	0.03834	46742	CORP	Real Estate	BBB+	0.35	0.371641123	0.03166		CAD	CANADA	FIXED
Dashboard

Cell Formulas
Range	Formula
A32:O33	A32	=IFERROR(INDEX('CORE PORT.xlsm]PORTF'!$A$8:$AA$1000, AGGREGATE(15,6,ROW('CORE PORT.xlsm]PORTF'!$A$8:$A$1000)/ IF($S$32="All",1,(YEAR('CORE PORT.xlsm]PORTF'!$E$8:$E$1000)>=$S$32))/ IF($S$33="All",1,(YEAR('CORE PORT.xlsm]PORTF'!$E$8:$E$1000)<=$S$33))/ IF($S$34="All",1,(('CORE PORT.xlsm]PORTF'!$S$8:$S$1000=$S$34)+('CORE PORT.xlsm]PORTF'!$S$8:$S$1000=$S$34&"-")+('CORE PORT.xlsm]PORTF'!$S$8:$S$1000=$S$34&"+")))/ IF($S$35="All",1,('CORE PORT.xlsm]PORTF'!$G$8:$G$1000>$S$35))/ IF($S$36="All",1,('CORE PORT.xlsm]PORTF'!$Q$8:$Q$1000=$S$36))/ IF($S$37="All",1,('CORE PORT.xlsm]PORTF'!$R$8:$R$1000=$S$37))/ IF($S$38="All",1,('CORE PORT.xlsm]PORTF'!$V$8:$V$1000=$S$38)), ROWS(A$32:A32)),MATCH(A$31,'CORE PORT.xlsm]PORTF'!$A$8:$AA$8,0)),"")

 

[KRice]

I added a 4th item that is critical.

 

[gcefaloni]

I added a 4th item that is critical.

Bingo.

I didn't think about the fact that the row not starting at 1 would be an issue in the aggregate function. But of course, yea, therein lied the rub.
I'm not that familiar with the aggregate function so understanding how all the components work together isn't to the level it should be yet.

Thank you so much once again!

Extremely appreciated

 

[KRice]

You're welcome...I'm glad this is working now.

 

[gcefaloni]

You're welcome...I'm glad this is working now.

Not as much as me... trust me