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.
Best Answers
-
@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)))))
Answers
-
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! 👍️