# How to find the Max Valor using INDEX and MATCH?

Options
✭✭✭✭

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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

Regards

Rodolfo

«1

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 05/20/22
Options

Try adding the MAX function like so:

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
edited 05/20/22
Options

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.

• ✭✭✭✭
Options

I tried typing it manually and get the same value

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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}:

• ✭✭✭✭
Options

here is the update

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

@Mike tv

Yes both are text/number

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

On Monday I will try again

Regards

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!