Hi all,

I'm hoping there's a simple solution for this one!


I have a list of items with values. As an example, let's say the items are cars. Each car can have one driver.

Problem: To pair the most valuable cars with the best suited driver available.



Value Car Best Driver 2nd 3rd 4th Last
62,000 Audi Jon Keith Tom Bill Paul
48,000 BMW Bill Tom Rob Paul Greg
23,000 Buick Greg Bill Paul Jordan Adam
25,000 Ford Bill Jon Rob Tom Jordan
32,000 GMC Jon Keith Adam Rob Tom
42,000 Mustang Keith Rob Henry Jon Greg
65,000 Tesla Bill Henry Keith Rob Jon
28,000 Toyota Keith Adam Greg Henry Bill

Selection Rank Formula Column (Best Avail Driver)
BMW (3) Tom (Bill is taken)
Mustang (4) Keith
Tesla (1) Bill
Audi (2) Jon
Ford (5) Rob (Bill and Jon are taken)

First, the formula needs to rank the cars, based on their value (I'm guessing the Rank or Countif formula)
Then, find the preferred driver in the table array in the Best Driver column.
If a higher ranked car has that driver, then move onto the next best driver (2nd). etc.

Thanks in advance.