INDEX MATCH Error

[rikapple]

I am trying to create a formula to create a top ten display. I want this to be calculated automatically.

I have the office names in column C and the score in column K. Would like the result to go into column S & T.

I think I can use the Index / Match function but I think this falls down when I get duplicate numbers in column K.

I can get the top ten numbers displayed easily enough by using the LARGE function, but want the corresponding office name from column C to be displayed in order next to the number.

A long after noon and I am nowhere any ideas anyone.

I hope this makes sense!1.JPG

Thank you in advance for any help that can be given

Rebecca x

[Glenn Kennedy]

Please post a sheet, not a jpg...

[Speshul]

Try this, i can't make out your row numbers in the picture so you will need to modify it

Put the formula in your Rank 1 cell and copy down as far as you need.
=IFERROR(INDEX(C1:C15,MATCH(LARGE(K1:K15,ROW(1:1)),K1:K15,0)),"")

[rikapple]

Hi

I tried what you said and it worked to an extent, but it is getting confused by duplicate numbers.

I have included the file if this helps any?

Sorry RE the picture, I am not very experienced.

R x

[cbatrody]

Hi,

Please see the attached file, I have added a helper column for ranking the range and then top 10.

In Column M: RANK(L10,$L$10:$L$26,0)+COUNTIF($L$10:L10,L10)-1
Column S: =INDEX($B$8:$M$26,MATCH(ROW(A1),$M$8:$M$26,0),2)

[rikapple]

Thank you! Much appreciated.
Worked perfectly!

[cbatrody]

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful.

[rikapple]

Hi you helped me yesterday with this issue. This has worked perfectly, but I am not trying to do the same again but with a different column.

What would I need to change to create the same process but this time just using numbers in column D?

Rx

[oeldere]

#5 from cbatrody

In Column M: RANK(L10,$L$10:$L$26,0)+COUNTIF($L$10:L10,L10)-1

Change the L in D =>

In Column M: RANK(D10,$D$10:$D$26,0)+COUNTIF($D$10:D10,D10)-1

[rikapple]

Hi

This did work to an extent but only did the top three names the rest of the top ten was just #?N/A

Must be something simple im sure but not seeing it.

Rx

[rikapple]

I have included a sample file to explain the problem.

I am trying to create a table to each of the columns listed E - J.

Obviously from the offices in Column C.

Thanks again for any help.

Rebecca x

[cbatrody]

Column "D" does not contain any data in the file which you had uploaded. Are you trying to SORT by "DHU Score" which is column E?

[cbatrody]

Hi,

Please see the updated file attached.

[rikapple]

Hi

Really sorry my fault as I was not very clear, its the names of the offices I want to prioritise. So the office with the highest number would be at the top working down.

As in example (in yellow) it works but only for the first 2 occurrence's.

I very much appreciate all your help in this matter.

Rebeccaxx

[rikapple]

Hi

Really sorry my fault as I was not very clear, its the names of the offices I want to prioritise. So the office with the highest number would be at the top working down.

As in example (in yellow) it works but only for the first 2 occurrence's.

I very much appreciate all your help in this matter.

Rebeccaxx

[oeldere]

@rikapple

Show the excel file without confidential information.

Please also add the desired (expected) result.

[micope21]

Hi

Why use Rank+Countif?

Not fair count 1 to 10

Rank should be like this 1,1,1,1,4,4,4,4,4,4,10

For top 10

AP10

=IFERROR(INDEX($C$10:$C$26,MATCH(1,INDEX(($L$10:$L$26=LARGE($L$10:$L$26,ROWS(AP$9:AP9)))*(COUNTIF(AP$9:AP9,$C$10:$C$26)=0),),0)),"")

copy down to row 10.

AQ10

=IFERROR(LARGE($L$10:$L$26,ROWS($1:1)),"")

copy down row 10

AO10

=IFERROR(RANK(AQ10,AQ$10:AQ$19,0),"")

copy down row 10.

Did same to
DHU Score,
Attendance,
Compliance,
Issues,
Turnover,
Fran Free.

See the file!

Regard