How to return highest value based on two columns in a range

Question

I’m trying to automate a spreadsheet to return the highest value out of a range based on a few criteria.

I want the highest “bid” for each “player” out of range of multiple players and bids. I also need it first to find the highest “year” and then take the highest value from that.

Here is a sample spreadsheet of what I’m talking about.

Sample Sheet

For example if I want the highest bid for “Joe Mixon” in the range B15:G40 I want to return the Owner, highest year and highest bid returned in cells E3:G3

Hopefully this makes sense! And any advice would be amazing!

Thanks!

Answer 1

use:

=INDEX(IFNA(VLOOKUP(B3:B12&C3:C12&D3:D12, 
 SORT({B16:B&C16:C&D16:D, E16:G}, 3, 0, 4, 0, 2, 0), {2, 3, 4}, 0)))

where

3, 0, 4, 0, 2, 0

Year, descending, Bid Amount, descending, Owner, descending

eg. priority is:

Year > Bid Amount > Owner

Answer 2

Assuming you want the latest year as a priority over latest salary, then these formulas at the top of the respective column should work. See example tab on your googlesheet with myname on it.

owner:

=FILTER(E16:E,B16:B=B3,F16:F=F3,G16:G=G3)

year

=MAXIFS(F16:F,B16:B,B3)

Bid:

=MAXIFS(G16:G,B16:B,B3,F16:F,F3)

EDIT: Player() crushed it with one formula that only needed small modification. Pretty impressive....

=INDEX(IFNA(VLOOKUP(B3:B12&C3:C12&D3:D12,SORT({B16:B&C16:C&D16:D, E16:G}, 3, 0, 4, 0, 1, 0), {2, 3, 4}, 0)))