How to find the Max Valor using INDEX and MATCH?
Best Answers
-
I think you'd be better off using a MAX(COLLECT) here.
=MAX(COLLECT({tkpsRangeScore}, {tkpsRange}, Payroll@row))
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.
-
Hey Jeff thanks a lot for your help. I appreciate a lot your effort invested in this issue.
Regards
Rodolfo
Answers
-
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
-
What's the current formula you're working with that is grabbing the 67% score?
-
@Mike TV I use this
=INDEX({tkpsRangeScore}, MATCH(Nomina@row, {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.
-
Try adding the MAX function like so:
=INDEX(MAX({tkpsRangeScore}), MATCH(Nomina@row, {tkpsRange}, 0))
-
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.
-
I edited the formula shortly after posting my reply. Try it with the ) moved to the end as so:
=INDEX(MAX({tkpsRangeScore}), MATCH(Nomina@row, {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.
-
-
It worked on my end in a sample sheet. Can you post an image of the two cross-sheet reference ranges you're using?
-
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
-
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}:
-
-
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.
-
@Mike tv
Yes both are text/number
One of them is Primary Column, but let me switch.
On Monday I will try again
Regards
-
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
-
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% .
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!