Index Match results = WTF?

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
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
RS
31CriteriaParameters
32Min Mat YR2020
33Max Mat YR2040
34RatingA
35Spread >All
36Sector 1All
37Sector 2All
38Coupon TypeAll
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
ABCDEFGHIJKLMNO
31ID_CUSIPNameTickerCoupon RateMaturity DateSector 1Sector 2RatingRSK_BB_ISSUER_DEFAULTBB_5Y_DEFAULT_PROBYieldAsk SpreadBase CurrencyCOUNTRYCPN_TYP
32013051DW4CAD2 CADCADCADCAD2    
Dashboard
Cell Formulas
RangeFormula
A32:H32,K32:O32A32=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
8CUSIPPos MM$TICKERCOUPON RATEMATURITY DATEPX_LASTYIELDYAS_YLD_SPREADG-Spread Percentile between MIN/MAX Hist. Spread.YAS_BNCHMRK_SECURITY_DESDUR_ADJ_MIDPV01$PV01INT_ACCAmount MM$Inc AccrSECTOR 1SECTOR 2RatingNXT CPNCOUNTRYCPN_TYPBase CurrencySettleNAMEAvg CostCoupon Frequency
925675TAD9 1.000 DOLCN2.34%22-Jul-21CORPConsumer, CyclicalBBB - CANADAFIXEDCAD3-Sep-19DOLLARAMA INC100.3702
1047787ZBW1 0.444 DE2.70%12-Oct-21CORPIndustrialA - CANADAFIXEDCAD5-Feb-19JOHN DEERE CANADA FND IN100.1652
11154728AN9 1.100 CTLONE2.60%7-Nov-22CORPBanksA- - CANADAFIXEDCAD5-Feb-19CENTRAL 1 CREDIT UNION98.2002
12190330AC4 1.000 COCAPS5.00%3-May-23CORPDiversified Finan ServBBB - CANADAVARIABLECAD5-Feb-19COAST CAPITAL SV CRE UN102.9382
13013051DM6 0.200 ALTA3.10%1-Jun-24PROVALTAAA- - CANADAFIXEDCAD13-Mar-20PROVINCE OF ALBERTA107.7622
1480310ZAC3 0.600 SAPCN2.83%21-Nov-23CORPConsumer, Non-cyclicalBBB+ - CANADAFIXEDCAD5-Feb-19SAPUTO INC98.7882
1506415ELY9 1.000 BNS3.89%18-Jan-24CORPBanksBBB+ - CANADAVARIABLECAD5-Feb-19BANK OF NOVA SCOTIA101.8382
1607813ZBZ6 1.000 BCECN3.35%12-Mar-25CORPCommunicationsBBB+ - CANADAFIXEDCAD5-Feb-19BELL CANADA100.6502
17013051DW4 - ALTA2.55%1-Jun-27PROVALTAAA- - CANADAFIXEDCAD13-Mar-20PROVINCE OF ALBERTA106.3342
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
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
ABCDEFGHIJKLMNO
31CUSIPNameTickerCoupon RateMaturity DateSector 1Sector 2RatingPos MM$Inc AccrYieldAsk SpreadBase CurrencyCOUNTRYCPN_TYP
32013051DW4PROVINCE OF ALBERTAALTA2.55%2027-06-01PROVALTAAA-001.40% CADCANADAFIXED
3383179XAH1SMARTCENTRES REITSRUUCN3.83%2027-12-21CORPReal EstateBBB+0.350.3716411233.17% CADCANADAFIXED
Dashboard
Cell Formulas
RangeFormula
A32:O32A32=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:O33A33=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
RS
31CriteriaParameters
32Min Mat YR2020
33Max Mat YR2023
34RatingA
35Spread >All
36Sector 1All
37Sector 2All
38Coupon TypeAll
Dashboard
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
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
ABCDEFGHIJKLMNO
31CUSIPNameTickerCoupon RateMaturity DateSector 1Sector 2RatingPos MM$Inc AccrYieldAsk SpreadBase CurrencyCOUNTRYCPN_TYP
32013051DW4PROVINCE OF ALBERTAALTA0.025546539PROVALTAAA-000.01401 CADCANADAFIXED
3383179XAH1SMARTCENTRES REITSRUUCN0.0383446742CORPReal EstateBBB+0.350.3716411230.03166 CADCANADAFIXED
Dashboard
Cell Formulas
RangeFormula
A32:O33A32=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

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I added a 4th item that is critical.
 

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
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

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...I'm glad this is working now.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,590
Messages
5,625,674
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top