# Looking for a formula to rank data and output unique value

1. ## Looking for a formula to rank data and output unique value

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.

2. ## Re: Looking for a formula to rank data and output unique value

Hi,

Formula:
`Please Login or Register  to view this content.`

This would help you to get the rank, but have a doubt how you identify the driver is available or taken??

3. ## Re: Looking for a formula to rank data and output unique value

You need several steps to get the results you want.

a3
=RANK(B3,\$B\$3:\$B\$10)

i3
=INDEX(D3:H3,MATCH(0,INDEX(COUNTIF(I\$2:I2,D3:H3),),0))

b14
=INDEX(\$A\$3:\$A\$10,MATCH(\$A14,\$C\$3:\$C\$10,0))

c14
=INDEX(\$I\$3:\$I\$10,MATCH(\$A14,\$C\$3:\$C\$10,0))

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1