Formula For Matching Dates And Returning Values

  • Hi all,


    I am stuck with a problem that I cannot solve by myself. An explanation - I have a worksheet with a data sheet that has the values for a number of portfolios over a time period (one month)then I have a corresponding sheet for each portfolio (in the attached example I have included only portfolio 1). What I am trying to do is to make a formula in the sheet Portfolio 1 that matches the date in the Portfolio 1 sheet with the data sheet and by matching the date returning the value for the Portfolio 1 for the date ie date 2008-06-01 should return for portfolio 1 (from the data sheet) 500.


    I have tried with varying Index and matching formulas and I do believe that this is the way to go. Can anyone help?

  • Re: Formula For Matching Dates And Returning Values



    Wow, I am impressed - I did not know that you could do a double match from an index!! That is great!


    Thanx alot :)


    Robert

    .¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.·´¯'·.¸¸·´¯'·.¸¸.· ><((((º>

  • Re: Formula For Matching Dates And Returning Values


    In cell B3 of Portfolio1:


    =INDEX(Data!$A$1:$AF$28,MATCH($B$1,Data!$A$1:$A$28),MATCH(A3,Data!$A$1:$AF$1))


    and copy down.


    This uses the Portfolio number in cell B1 of the Portfolio sheet, which I assume would be changed for your other Portfolio sheets.


    NB. However, having one sheet per portfolio might hinder you in further data analysis.


    :-(( too slow, busy chatting while posting reply

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!