# Match/Index Day and Month

✭✭✭✭

Greetings Smartsheet Community:

I am requesting assistance with a formula. Based on the date of the transaction on sheet #2, I want to populate the rate onto the conversion rate column on sheet #2 using data from sheet #1. For example, on sheet #2, I want to populate 108.61 on sheet #2 if the transaction has a date that fall in July 2019. That is, transaction with a date of July 1, July 2, July 3,.... will pick up the rate of 108.61; transaction with date of August 1, August 2, August 3, .... will pick up the rate of 106.23; and so on. Ultimately, I want to calculate the US conversion amount. Hope this make sense. Thank you in advance for your help.

• ✭✭✭✭

@Paul Newcome Yes, we use the currency rate of the last day of each month.

• ✭✭✭✭✭✭

Try something like this...

=VALUE(JOIN(COLLECT({Sheet 1 Exchange Column}, {Sheet 1 Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE@row), IFERROR(YEAR(@cell), 0) = YEAR(DATE@row)))))

• ✭✭✭✭✭✭

Is there only 1 entry per month on Sheet 1?

• ✭✭✭✭

@Paul Newcome Yes, we use the currency rate of the last day of each month.

• ✭✭✭✭✭✭

Try something like this...

=VALUE(JOIN(COLLECT({Sheet 1 Exchange Column}, {Sheet 1 Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE@row), IFERROR(YEAR(@cell), 0) = YEAR(DATE@row)))))

• ✭✭✭✭

@Paul Newcome Thank you so much. The formula works perfect!

=VALUE(JOIN(COLLECT({Sheet 1 Exchange Column}, {Sheet 1 Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE@row), IFERROR(YEAR(@cell), 0) = YEAR(DATE@row)))))

• ✭✭✭✭✭✭

Excellent! Happy to help! 👍️