How to find the Max Valor using INDEX and MATCH?

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

    @Rodolfo Garcia

    I think you'd be better off using a MAX(COLLECT) here.

    =MAX(COLLECT({tkpsRangeScore}, {tkpsRange}, [email protected]))

    In English: Collect the values from tkpsRangeScore where the tkpsRange is the same as the Payroll value from this row, and then return the highest number.

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭
    Answer ✓

    @Jeff Reisman

    Hey Jeff thanks a lot for your help. I appreciate a lot your effort invested in this issue.

    Regards

    Rodolfo

«1

Answers

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    I have several evaluations and the people can try making better score, How could I get the best score for all of them?

    How to get this 83% be reflected instead of the first attempt with 67%

    Thanks

    Rodolfo

  • Mike TV
    Mike TV ✭✭✭✭✭

    @Rodolfo Garcia

    What's the current formula you're working with that is grabbing the 67% score?

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike TV I use this

    =INDEX({tkpsRangeScore}, MATCH([email protected], {tkpsRange}, 0))

    and returns the first attemp from a form.

    Then when user looks his score, he can Improve, and perform another test

    and gets the better score, but it doesn´t return the highest value.

  • Mike TV
    Mike TV ✭✭✭✭✭
    edited 05/20/22

    @Rodolfo Garcia

    Try adding the MAX function like so:

    =INDEX(MAX({tkpsRangeScore}), MATCH([email protected], {tkpsRange}, 0))

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike TV

    I wrote and is not working yet, the cell now shows a #Invalid Value


    I am no pretty sure what is missing.

    Thanks for your support.

  • Mike TV
    Mike TV ✭✭✭✭✭
    edited 05/20/22

    @Rodolfo Garcia

    I edited the formula shortly after posting my reply. Try it with the ) moved to the end as so:

    =INDEX(MAX({tkpsRangeScore}), MATCH([email protected], {tkpsRange}, 0))

    If that doesn't work, then manually type it out using your cross-reference links because sometimes SmartSheet gets goofy when copy-pasting formulas with cross-sheet references.

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike TV

    I tried typing it manually and get the same value


  • Mike TV
    Mike TV ✭✭✭✭✭

    @Rodolfo Garcia

    It worked on my end in a sample sheet. Can you post an image of the two cross-sheet reference ranges you're using?

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike TV

    One is the Payroll and Score from a Grid call tkps


    in the meantime I think in using another column Autonumbered to get the last version

    with MAX function

  • Mike TV
    Mike TV ✭✭✭✭✭

    @Rodolfo Garcia

    This is what I meant by showing your cross-sheet references. Here's the one I made for {tkpsRangeScore}:

    Here's the one I made for {tkpsRange}:


  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike TV

    here is the update


  • Mike TV
    Mike TV ✭✭✭✭✭

    @Rodolfo Garcia

    Sorry but I'm not exactly sure what is wrong. The column you're putting the formula into, is it a text/number column? If it's not, that could be the problem.

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike tv

    Yes both are text/number

    One of them is Primary Column, but let me switch.

    On Monday I will try again

    Regards

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike TV

    Just to give you an update, I recently checked and none of these columns are Primary Column


    So it is hard to find the way to get the most recent value. Using INDEX and MATCH

    Regards

    Rodolfo

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭✭✭

    @Mike TV


    One thing that I found is, it only works if in the source grid the max value is at the top

    And Only in this case it give back the 83.


    But when you have the max value in the bottom it returns 67% .