How to find the Max Valor using INDEX and MATCH?

Rodolfo Garcia

Jeff Reisman

@Rodolfo Garcia

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.

Rodolfo Garcia

@Jeff Reisman

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

Regards

Rodolfo

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

@Rodolfo Garcia

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

Rodolfo Garcia

@Mike TV I use this

https://community.smartsheet.com/discussion/comment/330525#Comment_330525

=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.

Mike TV

@Rodolfo Garcia

Try adding the MAX function like so:

=INDEX(MAX({tkpsRangeScore}), MATCH(Nomina@row, {tkpsRange}, 0))

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

@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(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.

Rodolfo Garcia

@Mike TV

I tried typing it manually and get the same value

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

@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

@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

@Mike TV

here is the update

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

@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

@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

@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% .