VLOOKUP for highest value

Nick Burrus

Can someone show me how to use a VLOOKUP for the highest value?

For example:

=VLOOKUP(LARGE([% of Target]1:[% of Target]16, 3), Name1:[% of Target]16, 2)

 

Basically by the LARGE([% of Target]1:[% of Target]16, 3) I am returning a value of 52% (which is the third highest value). This is automatic. =LARGE([% of Target]1:[% of Target]16, 3) works fine. 

 

After that, I wish to look into the table, and return the Name of the employee that matches the % of Target automatically. 

It keeps saying #NO MATCH. Any tips?

Nick Burrus

VLOOKUP doesn't work well with formulas just figured that out. 

So my workaround was to use the new RANKEQ by @Kara Lumley's team.

=RANKEQ([% of Target]1, [% of Target]1:[% of Target]16)

Used this and a report to SORT 1-3

Mike Wilday

What about using Max fin the formulat?

https://help.smartsheet.com/function/max

sean59916

If the Names were to the right of the % column, the vlookup would work to return the name - you could also use the following to return the employee name: =INDEX(Employee$1:Employee$5, MATCH(LARGE([% of Target]$1:[% of Target]$5, 1), [% of Target]$1:[% of Target]$5, 0))

This returns the highest value - change the value in the LARGE function to meet your needs, i.e. 2nd, 3rd, etc.

 

See below - I hope that helps?

Sean