Index Formula Question

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
In one of my current workbooks I am using I have a formula =INDEX(C14:C49,MATCH(LARGE(IF(MOD(ROW(C14:C49)-ROW(C14)+1,3)=0,IF(K14:K49>60,K14:K49-ROW(K14:K49)/10^5)),1),K14:K49-ROW(K14:K49)/10^5,0)-2)

Basically this formula tells me who had the highest amount of contracts for a particular plan, but it has to be more than 60 contracts. The highest number is listed for 1st place. To indicate the second place winner I use this formula in a seperate cell =INDEX(C14:C49,MATCH(LARGE(IF(MOD(ROW(C14:C49)-ROW(C14)+1,3)=0,IF(K14:K49>59,K14:K49-ROW(K14:K49)/10^5)),2),K14:K49-ROW(K14:K49)/10^5,0)-2)

The issue I am running across is that when two people tie for first place it isn't registering anything. What I need it to do is when two people tie for first place it lists both names and leaves second place empty. I was hoping someone could help me with this. Thanks!
2006 Recruiter Contract Summary.xls
CDEFGHIJKLMNOPQRS
14HillaryAdams
15TotalContractsNewProv.DPMContr.HardStatesAddPlanAddLoc.CPPOCPPSCPTotalPOPSC5CFDFHPHOTotalPlans
1620730199101698200200400373837111918551
17MikeKing
18TotalContractsNewProv.DPMContr.HardStatesAddPlanAddLoc.CPPOCPPSCPTotalPOPSC5CFDFHPHOTotalPlans
199580137815776440043
Sep 22-28th
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
actually, I just replicated it and made two people tie, it put one of them in first place and one of them in second place, I looks like it put the first person it comes too while going down the column as 1st place and the person after that second.
 
Upvote 0
I noticed that the criteria for the highest number is >60, whereas the criteria for the second highest is >59. Is this correct?
 
Upvote 0
Actually I noticed that too on this one tab, it was just a mistake and I already fixed that.
 
Upvote 0

Forum statistics

Threads
1,214,639
Messages
6,120,679
Members
448,977
Latest member
dbonilla0331

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top