Index Match results = WTF?

G

gcefaloni

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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
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?
 
Last edited:
Upvote 0
KRice said:
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?
Click to expand...
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1TRADE DATE31-May-20 DURATION P&LDAYMONTHYEARPOSITION FILE#N/AEND OF MONTH:Hard copy Closing and Accrued for current month
2SETTL_DT02-Jun-20INDEX DURATION#N/A ReviewYRNYMBUS P&L#VALUE!#VALUE!#VALUE!#VALUE!Change Month P&L formula in cell I2
3ETF MODEL DUR7.61NYMBUS Return#VALUE!#VALUE!#VALUE!#VALUE!CORE PLUS PORTFOLIOArrange ACM and manually enter quantity in P&L cell
4LONG FACE15.575 MM$PORT DURATION#VALUE! YRXBB#VALUE!#VALUE!#VALUE! 129,054
5SHORT FACE0.000 MM$PORT PV01#VALUE! $MODEL ETF#VALUE!#VALUE!#VALUE!offset days for coupon
6NAV#VALUE! MM$1 BP MOVE#VALUE!Nymb Yield#VALUE!#VALUE!001-Jun-20
7NAV-Accrued#VALUE!Model ETF Yield3.57%XBB yield#VALUE! AA- #VALUE!#VALUE!#VALUE!#VALUE! 38,475
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-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!CORPConsumer, CyclicalBBB - CANADAFIXEDCAD3-Sep-19DOLLARAMA INC100.3702
1047787ZBW1 0.444 DE2.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!CORPIndustrialA - CANADAFIXEDCAD5-Feb-19JOHN DEERE CANADA FND IN100.1652
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?
 
Upvote 0
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?
 
Upvote 0
KRice said:
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?
Click to expand...

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?
 
Upvote 0
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.
 
Upvote 0
gcefaloni said:
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?
Click to expand...

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. ?‍♂️
 
Upvote 0
You're welcome and thanks for the update...that's good to hear.
 
Upvote 0
KRice said:
You're welcome and thanks for the update...that's good to hear.
Click to expand...

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! :)
 
Upvote 0
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+?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Fetch heads up stats between 2 teams
Replies
2
Views
109
AYouQueTai
A
JenniferMurphy
  • Question
How to synchronize two tables?
Replies
0
Views
102
JenniferMurphy
JenniferMurphy
J
  • Solved
adding cells based on day of the week
Replies
1
Views
70
Asbestos_Jen
Asbestos_Jen
JenniferMurphy
  • Question
Is there a Null function?
Replies
6
Views
206
JenniferMurphy
JenniferMurphy
A
  • Solved
Countifs? Count events in a range if something doesn't happen in another range
Replies
2
Views
145
AYouQueTai
A

Forum statistics

Threads
1,214,543
Messages
6,120,123
Members
448,947
Latest member
test111

Share this page

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
Back
Top