INDEX MATCH LARGE IF? Help please

[tjw23]

Hello

I am trying to work out a formula which will return a name in relation to a highest result in a column from a range of columns which will have data added to each week.

Having used this website before (thanks for the help) I have used the following formula which gives me the name in relation to the highest figure in one column:

=INDEX($A$8:$A$140,MATCH(LARGE(F$8:$F$140-ROW($F$8:$F$140)/10^5,1),$F$8:$F$140-ROW($F$8:$F$140)/10^5,0))

Column A being the list of names, column F being the column in which the numerical values are. The above also avoids issues with names who each have the same value relating to them. I have a top 10 list so it shows the top 10 names who have the highest values.

I now want to do the same thing but where the names in column A are coming from a range of data which is added to once a week. For instance say information is added each week in a year, I have 52 columns set up for data to be entered into. I want the largest (top 10 list) of names to be generated from the values entered into the most recent week. So in week 1 the names come from the data entered into week 1, then when week 2’s data is entered, the list of names automatically changes to pick up the top 10 from week 2 etc.

I have got a (probably not the best) formula using lots of “IFs” to automatically update the top 10 values, but I cannot figure out a formula to return the names relating to those values.

=IF(Z141>0,LARGE(Z8:Z140,1),IF(Y141>0,LARGE(Y8:Y140,1),IF….

I have tried incorporating the long IF formula into the INDEX MATCH LARGE formula above but I am not having any luck at the moment.

Any ideas would be much appreciated.

Thanks
tjw23

 

[ukmikeb]

Hi

Welcome to the MrExcel forum.

If you changed your IF statement to :-

=LARGE(INDEX((X8:X140,Y8:Y140,Z8:Z140),,,MATCH(0,X141:Z141,-1)),1)

such that only one of X141:Z141 contained 1 and the rest contained 0 to activate the relevant column, would that help?

 

[tjw23]

Thanks for the reply Mike.
i have tried your suggestion but it doesn't appear to be working (at least the way I have tried it).
Thank you though

 

[tjw23]

I have managed to get the following formula to give the correct names in order (top 10) with their relevant values for one week:
{=INDEX(A8:A140,MATCH(IF(F141>0,LARGE(F8:F140,1),0),F8:F140,0))}
Where F141 is the sum of the values in the range F8:F140

I just can't seem to get this to work when I extend it across the range of columns with more "Ifs". I am also not sure that would help me the names which have the same values (keep returning the same name).

 

[ukmikeb]

Hi

There was a slight problem with the suggestion that the non-relevant cells should contain zero, they should be empty.

Here are two examples :-

Excel Workbook
	A	B	C	D	E	F	G
19							John
20
21	Fred					7	393
22	Bill					22	7
23	Jim					9	33
24	Bert					157	9
25	John					200	200
26	Martin					40	41
27	Dave					75	66
28	Harry					131	153
29	Joe					29	92
30						670
tjw23

Excel 2007

Cell Formulas
Range	Formula
G19	=INDEX(A21:A29,MATCH(LARGE(INDEX((F21:F29,G21:G29),,,MATCH(0,F30:G30,-1)),1),INDEX((F21:F29,G21:G29),,,MATCH(0,F30:G30,-1)),0))
F30	=SUM(F21:F29)

Excel Workbook
	A	B	C	D	E	F	G
19							Fred
20
21	Fred					7	393
22	Bill					22	7
23	Jim					9	33
24	Bert					157	9
25	John					200	200
26	Martin					40	41
27	Dave					75	66
28	Harry					131	153
29	Joe					29	92
30							994
tjw23

Excel 2007

Cell Formulas
Range	Formula
G19	=INDEX(A21:A29,MATCH(LARGE(INDEX((F21:F29,G21:G29),,,MATCH(0,F30:G30,-1)),1),INDEX((F21:F29,G21:G29),,,MATCH(0,F30:G30,-1)),0))
G30	=SUM(G21:G29)

hth

 

[tjw23]

Thank you very much, I think that has done it.

Just a long formula to apply it to now!

 

[tjw23]

I thought this had worked but it doesn't seem to have done. It works perfectly when just two weeks are considered, but doesn't seem to when I apply the formula to more than two weeks. Perhaps I am going about it incorrectly though!? I don't think the fact that I have empty columns in between those that have data in (so F, H, J rather than F, G, H) is making a difference either as it seems to work fine when I just use two week's work of data.

This is what I have for 3 weeks, I eventually need 38 weeks:
{=INDEX(A8:A140,MATCH(LARGE(INDEX((F8:F140,H8:H140,J8:J140),,,MATCH(0,F141:J141,-1)),1),INDEX((F8:F140,H8:H140,J8:J140),,,MATCH(0,F141:J141,-1)),0))}

Any help would be much appreciated.

Thanks

 

[ukmikeb]

Hi

The intervening columns were causing the problem, however :-
tjw23[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
</thead><tbody>[TR]
[TH]21[/TH]
[TD]Fred[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]393[/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TH]22[/TH]
[TD]Bill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]153[/TD]
[/TR]
[TR]
[TH]23[/TH]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]33[/TD]
[TD][/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TH]24[/TH]
[TD]Bert[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]157[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TH]25[/TH]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD="align: right"]157[/TD]
[/TR]
[TR]
[TH]26[/TH]
[TD]Martin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TH]27[/TH]
[TD]Dave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TH]28[/TH]
[TD]Harry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]131[/TD]
[TD][/TD]
[TD="align: right"]153[/TD]
[TD][/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TH]29[/TH]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD][/TD]
[TD="align: right"]92[/TD]
[TD][/TD]
[TD="align: right"]131[/TD]
[/TR]
[TR]
[TH]30[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]923[/TD]
[/TR]
[TR]
[TH]31[/TH]
[TD]Martin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2007

Cell	Formula
J30	=SUM(J21:J29)
A31	=INDEX(A21:A29,MATCH(LARGE(INDEX(F21:J29,,MATCH(0,F30:J30,-1)),ROWS($A$31:$A31)),INDEX(F21:J29,,MATCH(0,F30:J30,-1)),0))

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

This change to the formula caters for that situation.

Remember that on the trigger row (row 30 above) the column on which you wish to operate is the ONLY one that has a value.

Also, if you wish the other names to appear then the data ranges have to have absolute references and the formula can then be dragged down.

hth

 

[tjw23]

Thank you very much. That works well but I have a problem with duplicate values now. It keeps returning the same name if the value is the same.

 

[ukmikeb]

Hi

I have managed to workaround this by adding a helper column :-

Excel Workbook
	A	F	G	H	I	J	K	L	M
21	Fred	7		393		66			6
22	Bill	22		7		153			3
23	Jim	9		33		92			5
24	Bert	157		9		9			9
25	John	200		200		157			2
26	Martin	40		41		200			1
27	Dave	75		66		40			7
28	Harry	131		153		40			8
29	Joe	29		92		131			4
30						888
31	Martin
32	John
33	Bill
34	Joe
35	Jim
36	Fred
37	Dave
38	Harry
39	Bert
tjw23

Excel 2007

Cell Formulas
Range	Formula
A31	=INDEX($A$21:$A$29,MATCH(SMALL($M$21:$M$29,ROWS($A$31:$A31)),$M$21:$M$29,0))
M21	=RANK(INDEX($F21:$J21,,MATCH(0,$F$30:$J$30,-1)),INDEX($F$21:$J$29,,MATCH(0,$F$30:$J$30,-1)),0)+COUNTIF(INDEX($F$21:$J21,,MATCH(0,$F$30:$J$30,-1)),INDEX($F21:$J21,,MATCH(0,$F$30:$J$30,-1)))-1
J30	=SUM(J21:J29)

Column M is the helper column and the formula can be dragged down.

Applying the helper column somewhat simplifies the name extraction formula in A31.

hth