I have the following table of data with the row of focus highlighted in red. This is being done on Google Sheets.

enter image description here

With the following summary page and row of interest highlighted in red

enter image description here

The question is:

The answer for "Maximized holding period" is the year "IRR" has the highest percent, in this case, it would be year 5 (expanded an extra decimal it is higher than year 6). I want the displaying answer on the summary page to be "year 5", not 13.14%. How would I go about crafting a formula to make that happen?

  • 1
    Welcome to Web Applications. What web app are you using? Also please show what you have tried.
    – Rubén
    Jul 19 at 14:23
  • The is being done on Google Sheets. Haven't tried anything yet
    – Logan C
    Jul 19 at 16:50
  • 1
    From what you wrote it is clear that your current formula returns you 13.14%. of the column "Year 5" ... what is your current formula?
    – Daniele
    Jul 19 at 17:55
  • I believe there is some confusion. The issue is not solving for IRR, that has been done and for year 5, the answer is 13.14%. Maximized holding period is a different sheet that is a summary page. The maximized holding period is the year IRR is the highest. Instead of displaying the answer of 13.14% I want the answer to be the corresponding year. For a different analysis this may be year 7 and the summary would then display year 7 so it changes depending on the inputs
    – Logan C
    Jul 19 at 17:59
  • Hi, welcome to WebApps. You've haven't tried to solve your question and you've shown no research. There could be several ways to answer your question and there might not be an exact precedent/duplicate. But you can try with simple components and once you understand how they fit together, the combined logic is obvious. max-get the max value in IRR row; match get Column number for the max value; address - use Column# and row# to get max value address; indirect(address) - use the address (it's just a string) to return the max value.
    – Tedinoz
    Jul 20 at 3:40

1 Answer 1


Assuming the table you show is in A1:K8, use filter() like this:

=+filter( A1:K1, A7:K7 = max(A7:K7) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.