VLOOKUP for highest value

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.

V lookup

It keeps saying #NO MATCH. Any tips?

Find more posts tagged with

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Sheet Summary Formula not working
Hi - I am trying to calculate the ratio between the number of completed requests and deferred requests in a Smartsheet, and how to display this ratio on a Smartsheet dashboard. I added the summary fields for the Completed and Deferred requests using Sheet Summary. I already created the formula to calculate the ratio for…
Preserve cell data BEFORE Update Request is completed
Hi, I'm trying to figure out how to preserve cell data BEFORE an Update Request is executed. I was hoping to find functionality that would allow me to change the value of a cell in helper column WITH the value of the cell that is going to be updated once the Update Request is sent and subsequently executed.
Filtering for Linked Cells
I am trying to run a report for all of the cells in my "Last Contact Date" column that are not linked to another cell. We have separate teams who are populating separate sheets (SS Logs) so we can link the dates back here, but until they do, we'll just see the dot indicating it's linked with a blank value. The full sheet…