index/match to show high number to low number

[njlevi49]

In column b I use =INDEX($E$3:$E$21,MATCH(C3,$F$3:$F$21,0)) and column c I use =LARGE($F$3:$F$21,ROW(F2)) as you see if the numbers are the same it pulls the first set of name in the e column and their are someof the name missing. I am trying to match the names in column b with numbers in column c all data pulled from columns e and f. Any ideas on how to resolve to make this work. Thanks

A	B	C	D	E	F
1	Team Ranking	Points		Team Ranking	Points
2	14 Milnamow/Batcheider	18.5		1 Isbister/Howe	15
3	6 Emeott/Hahn	18		2 Fitch/Poet	7
4	8 Budd/Rudnick	16		3 Hanlon/Dougherty	2
5	1 Isbister/Howe	15		4 Ponkey/Levi	20
6	18 Conway/Daughtery	14		5 Raukar/Itami	4
7	16 Barker/Supron	13		6 Emeott/Hahn	18
8	9 Friedrichs/Dennis	12		7 Grodzicki/Campau	6
9	11 Patterson/Dattilo	11		8 Budd/Rudnick	16
10	11 Patterson/Dattilo	11		9 Friedrichs/Dennis	12
11	10 Gohl/Thompson	10		10 Gohl/Thompson	10
12	15 Thomas/Coates	9		11 Patterson/Dattilo	11
13	17 Curry/Karoub	8		12 Grodzicki/Scott	11
14	2 Fitch/Poet	7		13 Hiller/Mudie	4
15	7 Grodzicki/Campau	6		14 Milnamow/Batcheider	18.5
16	5 Raukar/Itami	4		15 Thomas/Coates	9
17	5 Raukar/Itami	4		16 Barker/Supron	13
18	3 Hanlon/Dougherty	2		17 Curry/Karoub	8
19	3 Hanlon/Dougherty	2		18 Conway/Daughtery	14

<colgroup><col style="width: 19pt; mso-width-source: userset; mso-width-alt: 914;" width="25"> <col style="width: 147pt; mso-width-source: userset; mso-width-alt: 7168;" width="196"> <col style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;" width="46"> <col style="width: 48pt;" width="64"> <col style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;" width="190"> <col style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;" width="46"> <tbody>
</tbody>

 

[FDibbins]

Hi

Under B, you have 2 entries for 11 Patterson/Dattilo, but that name only appears once under E?

 

[njlevi49]

yes that is true and I am trying to figure how to write the formula to pull the correct name with the correct number, the formula is pulling the first name that matches the number. you will notice that under column e 12 Grodzicki/Scott has the same number 11 I cannot figure how to write the formula to match both names.

 

[AD_Taylor]

Try this small modification:

=INDEX($E$2:$E$20,MATCH(C2,$F$2:$F$20,0) + COUNTIF($C$2:C2, C2) - 1)

 

[FDibbins]

try this...

add a helper column in G and use this, copied down...
=IF(COUNTIF($F$2:F2,F2)>1,F2+(COUNTIF($F$2:F2,F2)/100),F2)

then use this C, copied down...
=LARGE($G$2:$G$19,ROW(A1))

 

[meldoc]

Hello

If you can add an helper column, try this.

Excel Workbook
	B	C	D	E	F	G
1	Team Ranking	Points		Team Ranking	Points
2	3 Hanlon/Dougherty	18		1 Isbister/Howe	15	5
3	13 Hiller/Mudie	17		2 Fitch/Poet	7	14
4	5 Raukar/Itami	16		3 Hanlon/Dougherty	2	18
5	7 Grodzicki/Campau	15		4 Ponkey/Levi	20	1
6	2 Fitch/Poet	14		5 Raukar/Itami	4	16
7	17 Curry/Karoub	13		6 Emeott/Hahn	18	3
8	15 Thomas/Coates	12		7 Grodzicki/Campau	6	15
9	10 Gohl/Thompson	11		8 Budd/Rudnick	16	4
10	12 Grodzicki/Scott	10		9 Friedrichs/Dennis	12	8
11	11 Patterson/Dattilo	9		10 Gohl/Thompson	10	11
12	9 Friedrichs/Dennis	8		11 Patterson/Dattilo	11	9
13	16 Barker/Supron	7		12 Grodzicki/Scott	11	10
14	18 Conway/Daughtery	6		13 Hiller/Mudie	4	17
15	1 Isbister/Howe	5		14 Milnamow/Batcheider	18.5	2
16	8 Budd/Rudnick	4		15 Thomas/Coates	9	12
17	6 Emeott/Hahn	3		16 Barker/Supron	13	7
18	14 Milnamow/Batcheider	2		17 Curry/Karoub	8	13
19	4 Ponkey/Levi	1		18 Conway/Daughtery	14	6
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=INDEX($E$2:$E$20,MATCH(C2,$G$2:$G$20,0))
C2	=LARGE($G$2:$G$19,ROW(G2)-1)
G2	=RANK(F2,$F$2:$F$19)+COUNTIF(F$2:F2,F2)-1

 

[AD_Taylor]

Checked my answer and it wasn't actually working

So this small adjustment:

Add a helper column in G with this formula
=RANK(F3,$F$3:$F$20) + COUNTIF($F$3:F3, F3) - 1

Then in B use:
=INDEX($E$3:$E$21,MATCH(ROW(B1),$G$3:$G$21,0))

And in C use:
=INDEX($F$3:$F$21, MATCH(ROW(C1), $G$3:$G$21, 0))

Hope this helps!

 

[njlevi49]

I used =INDEX($E$2:$E$20,MATCH(C2,$F$2:$F$20,0) + COUNTIF($C$2:C2, C2) - 1) but I still get duplicates. Column D and E are the raw data that I use to get the data into Columns A and B. The other formulas creating a helper column is giving me ranking which I am not looking for ranking. I need to pull from column e and populate the LARGEST number at the top and match that names with those numbers. What would cause the duplicates and leavel out names. Column A8 should read

6 Emeott/Hahn

14.0

<tbody>
</tbody>

	A	B	C	D	E
1	Team Ranking	Points		Team Ranking	Points
2	18 Conway/Daughtery	20		1 Isbister/Howe	18.0
3	1 Isbister/Howe	18		2 Fitch/Poet	17.0
4	2 Fitch/Poet	17		3 Hanlon/Dougherty	16.0
5	3 Hanlon/Dougherty	16		4 Ponkey/Levi	15.0
6	4 Ponkey/Levi	15		5 Raukar/Itami	14.0
7	5 Raukar/Itami	14		6 Emeott/Hahn	14.0
8	5 Raukar/Itami	14		7 Grodzicki/Campau	12.0
9	7 Grodzicki/Campau	12		8 Budd/Rudnick	11.0
10	8 Budd/Rudnick	11		9 Friedrichs/Dennis	10.0
11	9 Friedrichs/Dennis	10		10 Gohl/Thompson	9.0
12	10 Gohl/Thompson	9		11 Patterson/Dattilo	8.0
13	11 Patterson/Dattilo	8		12 Grodzicki/Scott	7.0
14	12 Grodzicki/Scott	7		13 Hiller/Mudie	6.0
15	13 Hiller/Mudie	6		14 Milnamow/Batcheider	5.0
16	14 Milnamow/Batcheider	5		15 Thomas/Coates	4.0
17	15 Thomas/Coates	4		16 Barker/Supron	3.0
18	16 Barker/Supron	3		17 Curry/Karoub	2.0
19	17 Curry/Karoub	2		18 Conway/Daughtery	20.0
				Raw data pulled from other parts of sheet

<colgroup><col style="width: 17pt; mso-width-source: userset; mso-width-alt: 824;" width="23"> <col style="width: 126pt; mso-width-source: userset; mso-width-alt: 5973;" width="168"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 130pt; mso-width-source: userset; mso-width-alt: 6172;" width="174"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>

 

[AD_Taylor]

We understand this is what you want, but in order to show the points highest to lowest you need to use RANK at least somewhere. Try the formulas I posted in post #7, just above your last post.

What this is does is use Column G to get the ranks of the points and then convert these ranks back to points in Column B. Therefore it is the actual points values that appear in column B not the ranks.

Hope this helps!

 

[meldoc]

Hello again

As you don't object to helper columns, try this with two.

Excel Workbook
	A	B	C	D	E	F	G
1	Team Ranking	Points		Team Ranking	Points
2	4 Ponkey/Levi	20		1 Isbister/Howe	15	14	18
3	14 Milnamow/Batcheider	18.5		2 Fitch/Poet	7	5	17
4	6 Emeott/Hahn	18		3 Hanlon/Dougherty	2	1	16
5	8 Budd/Rudnick	16		4 Ponkey/Levi	20	18	15
6	1 Isbister/Howe	15		5 Raukar/Itami	4	2	14
7	18 Conway/Daughtery	14		6 Emeott/Hahn	18	16	13
8	16 Barker/Supron	13		7 Grodzicki/Campau	6	4	12
9	9 Friedrichs/Dennis	12		8 Budd/Rudnick	16	15	11
10	12 Grodzicki/Scott	11		9 Friedrichs/Dennis	12	11	10
11	11 Patterson/Dattilo	11		10 Gohl/Thompson	10	8	9
12	10 Gohl/Thompson	10		11 Patterson/Dattilo	11	9	8
13	15 Thomas/Coates	9		12 Grodzicki/Scott	11	10	7
14	17 Curry/Karoub	8		13 Hiller/Mudie	4	3	6
15	2 Fitch/Poet	7		14 Milnamow/Batcheider	18.5	17	5
16	7 Grodzicki/Campau	6		15 Thomas/Coates	9	7	4
17	13 Hiller/Mudie	4		16 Barker/Supron	13	12	3
18	5 Raukar/Itami	4		17 Curry/Karoub	8	6	2
19	3 Hanlon/Dougherty	2		18 Conway/Daughtery	14	13	1
Sheet1

Excel 2010

Cell Formulas
Range	Formula
A2	=INDEX($D$2:$D$19,MATCH(G2,$F$2:$F$19,0))
B2	=INDEX($E$2:$E$19,MATCH(A2,$D$2:$D$19,0))
F2	=RANK(E2,$E$2:$E$19,1)+COUNTIF(E$2:E2,E2)-1
G2	=LARGE($F$2:$F$19,ROWS(F$2:F2))