Forum Discussion

RichardR2060's avatar
RichardR2060
Copper Contributor
Mar 30, 2025
Solved

Index & Match Function

I am trying to calculate a percentage return using the following formula: =INDEX($L$51:$L$222,MATCH($N1,$B$51:$B$222,0))/INDEX($L$51:$L$222,MATCH(EOMONTH($N1,-60), $B$51:$B$222,0))-1   I am gettin...
BI & Data Analysis
excel
Formulas and Functions
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Mar 31, 2025

    Perhaps there are no data for 36 and 60 months ago?

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

HansVogelaar's avatar
HansVogelaar
MVP
Mar 31, 2025

Perhaps there are no data for 36 and 60 months ago?

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

  • RichardR2060's avatar
    RichardR2060
    Copper Contributor
    Apr 04, 2025

    Thank you, Hans. Problem solved.

    However, I have another question. I am calculating standard deviation for a data set. I have tried the following two formulas on the same data set. However, I am getting different results. Any thoughts as to why? Thank you.

    =STDEV.P(FILTER(D$48:D$437,$A$48:$A$437>EOMONTH($B7,-60)))*SQRT(12)

    =STDEV.P(D146:D206)*SQRT(12)

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 04, 2025

      Hi Richard,

      That is impossible to answer without seeing a copy of the workbook.

Resources