Index Match Multiple Criteria Including MIN, on a Table

  • Hi Guys and Gals!!!

    I have two goals operating but to focus on one, I'm trying to create a PIVOTCHART from a dataset. On the tab titled "Where the Action Is", you will find a table set up for a Pivot Chart. In column "Maths" of that table, I want to ultimately calculate a new market value based on a percent change on the current price from its minimum date. To put it mathematically:

    To pick a random record, lets use MSCI ACWI for 1/9/2019. The Maths cell should = 1000000*(1+(66.550003-64.139999)/64.139999)
    ...for any MSCI ACWI (or any index) record, the % change should be calculated against the minimum date, which for MSCI ACWI is (1/2/2019 = 64.139999)

    The idea here is I could create a pivot chart of the hypothetical growth of $1MM for any given index.

    I've been trying to kinda start with the "core" of the formula which would be to index(match) the price for the minimum date of an index and keep getting an #N/A error:

    [SIZE=12px]=INDEX([Price],MATCH(1,INDEX(([Index]="MSCI ACWI")*([Date]=MIN(IF([Index]="MSCI ACWI",[Index]))),0),0))[/SIZE]

    Other than that... if you're interested :D please take a look at the 'Outputs' tab. My boss basically wants to be able to see what our asset allocation performance would be, assuming market performance. There is an allocations table, which would allow me to edit (on the fly) how much of our portfolio is subject to each index. Below that you will see three calculation methods: YTD, Monthly, and Weekly. In each, you will see what is essentially a math problem for each portfolio, which is [SIZE=11px]Starting Value * Allocation * 1+Performance + n times[/SIZE]. Same thing for the Prior Period, then basic math problems for % change, nominal change, and day's cash.

    YTD should always be CP = max dates for indices against min dates for indices | PP = start value
    Monthly should always be CP = dropdown of months (January, February, etc), and automatically select the max date for that month and calculate performance against min date for the index... so July % change would be July 31 vs. Jan 2) | PP exact same except using max date for preceding month
    Weekly should always be CP = dropdown of dates from table2, automatically calculate previous period date as 7 days prior, math similar to above

    I kinda started to attempt some VBA to do this, because these values don't necessarily need to be stored to be readily available for a chart or something... if someone wants a chart, I could just run a few periods through Monthly or Weekly to cull the data. The problem with that is I'm not that great at VBA, lol

    [SIZE=24px]Thanks in Advance For All Your Help!!![/SIZE]

  • Hi,

    Very confusing message ...:o2

    Always safer to stick to a single topic per thread ...

    Regarding your first question about your Array formula ...

    Could you describe the conditions to apply ... your expected result and provide as an example the result for row 26 ... ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim, sorry!

    So line 26 won't necessarily work since that is the beginning of the dataset. Best to start with line 27: 1/3/2019 | MSCI ACWI | 63.049999 | #N/A

    Cell D27 should = 1000000 * (1+(63.049999 - 64.139999) / 64.139999)

    A similar condition should also be true for, lets say cell D37, for example = 1000000 * (1+(67.519997 - 64.139999) / 64.139999)

    These are % change formulas that are computing a new market value.... I've highlighted in red the 1/2/2019 pricing date. So ALL formulas should reference the earliest date for that index. You will notice several indices on this table: MSCI ACWI, Russell 1000, etc. The earliest date for each index has a different price.

    So there are a few things going on here:
    (1) Perform % change calculation with current price (price on this line/record) against the earliest price for this index.
    (2) Calculate new Market Value with that % change (this is the 1000000 * (1 + ...) part

    Since there are 2 matches to make (index and minimum date), I've been attempting to use an Index(Match) formula, but it hasn't been working... this isn't the actual formula I'm using but it is the logical flow, and I've tried it as an array formula too:


  • Hi,

    In cell D26 ... you can have following formula .

    1. =1000000 * (1+(C26 -(INDIRECT("C"&MATCH(B26,$B:$B,0)))) / (INDIRECT("C"&MATCH(B26,$B:$B,0))))

    And copy it down .. till D814 ...

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,

    I've entered the formula exactly as you presented and it does calculate a value. But I'm unfamiliar with how indirect is being used in this case... I see where its matching the index name but how is it determining which date to capture? Could you explain how this formula is working, just for me to learn and grow?

    Also, I've done a manual calculation on my end (still in excel) and found that the two methods do not agree... for example, doing the math manually on row 29: 1/7/2019 | MSCI ACWI | 65.459999

    The formula you shared produced 1,036,171...

    Calculating it manually: 1000000 * (1 + (C29 - C26) / C26) = 1,020,580


    How is this working?! Like... now its working perfectly, but I just don't understand how the formula is picking the correct denominator for the % change formula!

    Great ... :smile:

    Now that you have got the formula working properly ... and producing the expected results ... we can spend a couple of minutes about its underlying logic ...

    In all instances, based on the Index located in Column B, you do need to get the first row where this index is located ... the Match() function does return this Row number ...

    However the Price you need ... is located in Column C ... Indirect allows to combine the Column C with the Row number found in Column B ...

    For example, if in any cell ... you type in =Indirect(C26) ... the formula will return the value located in C26 ... e.g. 64.139999

    Hope this explanation is clear enough :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)