Index Match Large function questions for duplicate values

[Killswitch69]

I am trying to use the index, match and large functions. I am running into issue with duplicate values. I have columns that can be used as tie breakers but I just don't know how to figure it our.

Here is my formula : =INDEX($T$5:$T$20,MATCH(LARGE(T5:T20,1),T5:T20,0))

Now if column U is higher then it breaks the tie if that is tied then higher value in column R.

Is this possible?

 

[Andrew Poulsom]

Welcome to Mr Excel.

"If column U is higher" than what?

 

[Killswitch69]

Thank you for the quick response. Sorry, I should have explained better. if t5 = 268 and t6 = 268 then the tie breaker should be U5 and U6, so the highest value in U. I will make a sample spread sheet and attach if I can do that.

 

[Killswitch69]

I can't attach a file so here is a sample and expected result.

Person A , 20, 1 , 10 , 2 , 30 , 3
Person B , 19, 2 , 10 , 2 , 29 , 4
Person C , 19, 2 , 11 , 4 , 30 , 6

result:

Person C , 30 , 6
Person A , 30 , 3
Person B , 29 , 4

If F1, F3 tie G1, G3 break tie if still tie then E1 and E3 break tie. All ties break by highest value in the cell matched.

 

[Andrew Poulsom]

For that scenario:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Person A	20	1	10	2	30	3		Person C	30	6
2	Person B	19	2	10	2	29	4		Person A	30	3
3	Person C	19	2	11	4	30	6		Person B	29	4
Sheet1

Cell Formulas
Range	Formula
I1	=INDEX(A$1:A$3,MATCH(LARGE(INDEX(F$1:F$3+(G$1:G$3/10),),ROWS(I$1:I1)),INDEX(F$1:F$3+(G$1:G$3/10),),0))
J1	=INDEX(F$1:F$3,MATCH(LARGE(INDEX(F$1:F$3+(G$1:G$3/10),),ROWS(I$1:I1)),INDEX(F$1:F$3+(G$1:G$3/10),),0))
K1	=INDEX(G$1:G$3,MATCH(LARGE(INDEX(F$1:F$3+(G$1:G$3/10),),ROWS(I$1:I1)),INDEX(F$1:F$3+(G$1:G$3/10),),0))

 

[Killswitch69]

wow, that is impressive. Thank you so much.
one question as I am deciphering your formula.
How do you change it to put the formulas in the cells like below?

Worksheet Formulas

Cell	Formula
A22	=INDEX(A$1:A$3,MATCH(LARGE(INDEX(F$1:F$3+(G$1:G$3/10),),ROWS(I$1:I1)),INDEX(F$1:F$3+(G$1:G$3/10),),0))
I22	=INDEX(F$1:F$3,MATCH(LARGE(INDEX(F$1:F$3+(G$1:G$3/10),),ROWS(I$1:I1)),INDEX(F$1:F$3+(G$1:G$3/10),),0))
J22	=INDEX(G$1:G$3,MATCH(LARGE(INDEX(F$1:F$3+(G$1:G$3/10),),ROWS(I$1:I1)),INDEX(F$1:F$3+(G$1:G$3/10),),0))

<thead>
</thead><tbody>
</tbody>

 

[Killswitch69]

Actually I figured out how to change the output cell, but still have a question.

If I changed the index ranges from rows 5 through 20 and any of the rows are emtpy it will not work. Is there a work around for ignoring empty cells?

 

[Andrew Poulsom]

In what way didn't it work and with what data?

 

[Killswitch69]

How can I show you my spreadsheet on this forum if I am not allowed to post an attachment? I think it would be easier than trying to explain. I think it may have something to do with this argument ROWS(I$1:I1) but I'm not sure. below is the formula I have now. It work just fine except if there is missing data. columns ABC are merged for names, DEFGH are data and calculated into I and J. all from rows 5 through 20. Index formula are to sort and copy data in order from highest with the tie breaker you have figured out into the top four scores into Rows 22,23,24 and 25 and columns ABC merged, I and J. Hope this makes sense.

Cell ABC-22(merged) =INDEX(I$5:I$20,MATCH(LARGE(INDEX(I$5:I$20+(J$5:J$20/10),),ROWS(I$5:I5)),INDEX(I$5:I$20+(J$5:J$20/10),),0))
Cell I22 =INDEX(I$5:I$20,MATCH(LARGE(INDEX(I$5:I$20+(J$5:J$20/10),),ROWS(I$5:I5)),INDEX(I$5:I$20+(J$5:J$20/10),),0))
Cell J22 =INDEX(J$5:J$20,MATCH(LARGE(INDEX(I$5:I$20+(J$5:J$20/10),),ROWS(I$5:I5)),INDEX(I$5:I$20+(J$5:J$20/10),),0))

Then 3 more on rows 23,24,25 for the top 4 scores.

The work around that I found is to fill in junk data is all the cells. Without all rows filled in between 5 and 20 the formula returns #VALUE!

And thanks so much for your interest, I would have never gotten this far without your help.

 

[Andrew Poulsom]

Do you want to put your workbook on a share like Box.com and post the URL? Blank cells in the range shouldn't make a difference, but formula blanks will because you can't divide null by 10.