# VLOOKUP for highest value

✭✭✭✭✭✭
edited 12/09/19

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?

Dr. St Nicholas Burrus DHA, PMP

I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

• ✭✭✭✭✭✭

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

Dr. St Nicholas Burrus DHA, PMP

I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.

• ✭✭✭✭✭✭

What about using Max fin the formulat?

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

• ✭✭✭
edited 07/03/19

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