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 don't quite follow everything, but I notice that index refers to '[CORE PORT.xlsm]PORTF'!$A$8:$AA$1000 and all of the arrays in AGGREGATE refer to ranges 9:1000. Is there a reason for that? I would expect to see arrays of the same size. What does '[CORE PORT.xlsm]PORTF'!$A$7:$AA$10 look like...just the first few cells starting at row 7?...I'm wondering whether you have a mismatch between the index that AGGREGATE returns and where you are "zeroing" '[CORE PORT.xlsm]PORTF'!$A$8:$AA$1000.

Also have a look at this part of the formula:
MATCH(A$31,'[CORE PORT.xlsm]PORTF'!$A$8:$AA$8))
Why is A31 being matched on row 32?

 

[gcefaloni]

I don't quite follow everything, but I notice that index refers to '[CORE PORT.xlsm]PORTF'!$A$8:$AA$1000 and all of the arrays in AGGREGATE refer to ranges 9:1000. Is there a reason for that? I would expect to see arrays of the same size. What does '[CORE PORT.xlsm]PORTF'!$A$7:$AA$10 look like...just the first few cells starting at row 7?...I'm wondering whether you have a mismatch between the index that AGGREGATE returns and where you are "zeroing" '[CORE PORT.xlsm]PORTF'!$A$8:$AA$1000.

Also have a look at this part of the formula:
MATCH(A$31,'[CORE PORT.xlsm]PORTF'!$A$8:$AA$8))
Why is A31 being matched on row 32?

Hi KRice!

So, In "CORE PORT" (which is another workbook than the one in which the actual formulas with aggregate is being calculated), the first 7 rows are useless, just other information there as you can see in the table below. The row 8 is the header row and the table contents start on row 9. The table goes from column A to AA.

CORE PORT.xlsm
	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
1	TRADE DATE	31-May-20		DURATION			P&L	DAY	MONTH	YEAR					POSITION FILE	#N/A						END OF MONTH:		Hard copy Closing and Accrued for current month
2	SETTL_DT	02-Jun-20		INDEX DURATION	#N/A Review	YR	NYMBUS P&L	#VALUE!	#VALUE!	#VALUE!		#VALUE!												Change Month P&L formula in cell I2
3				ETF MODEL DUR	7.61		NYMBUS Return	#VALUE!	#VALUE!	#VALUE!		#VALUE!	CORE PLUS PORTFOLIO											Arrange ACM and manually enter quantity in P&L cell
4	LONG FACE	15.575	MM$	PORT DURATION	#VALUE!	YR	XBB	#VALUE!	#VALUE!	#VALUE!										129,054
5	SHORT FACE	0.000	MM$	PORT PV01	#VALUE!	$	MODEL ETF	#VALUE!	#VALUE!	#VALUE!										offset days for coupon
6	NAV	#VALUE!	MM$	1 BP MOVE	#VALUE!		Nymb Yield	#VALUE!	#VALUE!											0	01-Jun-20
7	NAV-Accrued	#VALUE!		Model ETF Yield	3.57%		XBB yield	#VALUE!	AA-						#VALUE!	#VALUE!		#VALUE!	#VALUE!	38,475
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	#N/A Requesting Data...	#VALUE!	#N/A Requesting Data...	#N/A Unclassified: Monthly limits exceeded, request is blocked.	#N/A Requesting Data...	#N/A Requesting Data...	#VALUE!	#VALUE!	#N/A Requesting Data...	#VALUE!	#VALUE!	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	#N/A Requesting Data...	#VALUE!	#N/A Requesting Data...	#N/A Unclassified: Monthly limits exceeded, request is blocked.	#N/A Requesting Data...	#N/A Requesting Data...	#VALUE!	#VALUE!	#N/A Requesting Data...	#VALUE!	#VALUE!	CORP	Industrial	A	-	CANADA	FIXED	CAD	5-Feb-19	JOHN DEERE CANADA FND IN	100.165	2
PORTF

As for the table with the aggregate function, the header row is row 31 and the contents of the dynamic table is starting in row 32 (that's where the index match aggregate formula is input). So I'm matching the name in this table in header row 31 with the name in the CORE PORT table's header row which is row 8 (going from column A:AA). Rows 1:30 are unrelated and there's another working table with a similar formula in there. I'm essentially just copying the same table as the one that's working in row 1:30 but trying to refer to another workbook (CORE PORT.xlsm) instead.


I know there's a lot of components in there, am I helpful at all with this answer?

 

[KRice]

Yes, that helps to clear up some things. What happens if you change the initial INDEX part to be INDEX('[CORE PORT.xlsm]PORTF'!$A$9:$AA$1000...? That would seem to make the arrays of equal size and I think make them correspond to each other.

About the column match question...and the formula MATCH(A$31,'[CORE PORT.xlsm]PORTF'!$A$8:$AA$8))
The formula appears in the Dashboard worksheet in cell A32 and copied across to several other cells to the right. A$31 then refers to the column header named "ID_CUSIP", and then the formula to the right in column B will refer to the header named "Name", etc. Now when we look at the PORTF worksheet, in A8:AA8, I don't see "ID_CUSIP"....I see "CUSIP". Is that the issue? You might try changing one of the column headings to match the other, but I'm not sure if that will cause issues anywhere else in your workbook?

 

[gcefaloni]

Yes, that helps to clear up some things. What happens if you change the initial INDEX part to be INDEX('[CORE PORT.xlsm]PORTF'!$A$9:$AA$1000...? That would seem to make the arrays of equal size and I think make them correspond to each other.

About the column match question...and the formula MATCH(A$31,'[CORE PORT.xlsm]PORTF'!$A$8:$AA$8))
The formula appears in the Dashboard worksheet in cell A32 and copied across to several other cells to the right. A$31 then refers to the column header named "ID_CUSIP", and then the formula to the right in column B will refer to the header named "Name", etc. Now when we look at the PORTF worksheet, in A8:AA8, I don't see "ID_CUSIP"....I see "CUSIP". Is that the issue? You might try changing one of the column headings to match the other, but I'm not sure if that will cause issues anywhere else in your workbook?

I tried putting the ROW and Index array at A8:AA1000 or both at A9:AA1000. Same result in both cases. Either way it doesn't work and B32 returns CAD (from COLUMN 23) when it should return John Deere Canada (Name) from Column 25. You are right about the first column, but that's just a naming issue and is easy to fix. My problem is that the columns with exactly the same headers in both tables are not finding eachother. Look at column B,C, D, E, F, G, H, etc. They are all the same name yet all returning wierd stuff like 2, CAD from column 23 or simply an error. Cell B32 referring to B31 (Name) should match with cell Y8 (column 25) in Workbook "Core PORT" and it doesn't. It instead returns column 23 which is base currency and that is why it returns CAD.

It's mindbuggling to me. Unless it is something related to the fact that we are targeting different workbooks. In which case, how would I fix that?

 

[KRice]

Have you tried populating a row on the Dashboard, in columns A:H and K:O with:
=MATCH(A$31,'[CORE PORT.xlsm]PORTF'!$A$8:$AA$8)
and copy across to the relevant columns?
Check to see if you get returns of 1, 25, 3, 4, 5, 17, 18, 19, 7, 23, 21, 22
Those should be the corresponding column numbers associated with matching column headers in the Dashboard sheet.
You might want to put the match "exact" option on that function:
=MATCH(A$31,'[CORE PORT.xlsm]PORTF'!$A$8:$AA$8,0)

Also check the "Ask Spread" column header. I see that on the Dashboard sheet, but not in the PORTF sheet.

 

[gcefaloni]

I tried putting the ROW and Index array at A8:AA1000 or both at A9:AA1000. Same result in both cases. Either way it doesn't work and B32 returns CAD (from COLUMN 23) when it should return John Deere Canada (Name) from Column 25. You are right about the first column, but that's just a naming issue and is easy to fix. My problem is that the columns with exactly the same headers in both tables are not finding eachother. Look at column B,C, D, E, F, G, H, etc. They are all the same name yet all returning wierd stuff like 2, CAD from column 23 or simply an error. Cell B32 referring to B31 (Name) should match with cell Y8 (column 25) in Workbook "Core PORT" and it doesn't. It instead returns column 23 which is base currency and that is why it returns CAD.

It's mindbuggling to me. Unless it is something related to the fact that we are targeting different workbooks. In which case, how would I fix that?

I'm a moron... I forgot about putting "Exact" parameter...

Thanks a lot... that solved it all... Sometimes, it really is just a comma that's wrong. ?‍

 

[KRice]

You're welcome and thanks for the update...that's good to hear.

 

[gcefaloni]

You're welcome and thanks for the update...that's good to hear.

Actually Kirk, if I may bug you a little bit more. The exact parameter fixed the column issue. I had not noticed that the rows seem to be an issue still though. When I filter for lets say "A" ratings in S34. It returns all kinds of wierd stuff AA-, BBB+, etc. So it doesn't refer to the proper rows in the Core Port table. Would that be linked to the aggregate formula? Or it is possibly the rows not lining up properly like you were suggesting? I tried putting the both the index array and the row array at 9:1000 or 8:1000, all returns the same thing. Same thing for the last row statement that does B$32:B32. I tried B$32:B33, B$32:B34, and always returns errors or wrong rows. So my filtering parameters don't seem to work for this table.

Any ideas?

Columns are working great though!

 

[KRice]

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+?