# Formula For Matching Dates And Returning Values

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• 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?

## Files

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

• Re: Formula For Matching Dates And Returning Values

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

## Files

• 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!