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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
I noticed that the criteria for the highest number is >60, whereas the criteria for the second highest is >59. Is this correct?
 

msk7777

Active Member
Joined
Mar 30, 2004
Messages
280
Actually I noticed that too on this one tab, it was just a mistake and I already fixed that.
 

Forum statistics

Threads
1,141,626
Messages
5,707,491
Members
421,511
Latest member
mgroah1

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
Top