If VLOOKUP finds two or more matching values in a column, can it compare which one has the highest value in a specific cell and then return the value from another cell?

Answer 1

To find the highest value in a column combine VLOOKUP and MAX as shown below. You can do the same with MIN to find the lowest value in a column. In this example you can also use the value in Sales to find the Salesperson simply by changing the column from 1 to 2.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Lookup Value A2:A9

Table Array A2:B9

Sales - Column Index 1

Salesperson - Column Index 2

Range Lookup - True = 0, False = 1

Sales Salesperson

 $3,776 Andrew Harper          

 $8,594 Anita Hanson              

    $901 Ronald Gardner

$10,146 Louise Carpenter      

 $3,039 Patrick Ross                

 $5,338 Kelly Anderson

    $901 =VLOOKUP(MIN(A2:A19),A2:B9,1,0)

$10,146 =VLOOKUP(MAX(A2:A19),A2:B9,1,0)

Ronald Gardner =VLOOKUP(MIN(A2:A19),A2:B9,2,0)

Louis Carpenter =VLOOKUP(MAX(A2:A19),A2:B9,2,0)