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

Quote from RichardSchollar

Hi

=INDEX(Data!$B$2:$AE$25,MATCH(Portfolio1!$B$1,Data!$A$2:$A$28,0),MATCH(Portfolio1!$A3,Data!$B$1:$AE$1,0))

In B3 copied down. See attached for the example.

Richard

Display More

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

Thanx alot

Robert