How do I handle a tie in an index/match function

[dugmic]

I have a fairly simple index/ match situation where I have a range with names and then corresponding sales totals. I am trying to come up with a way to automatically sort and rank them accordingly. So the Large formula works great for putting the data in descending order, and my indexand match formula works great to match the name with the corresponding sales figure. What I can't figure out is how do I handle when two or more people have the same sales amount. My formula wants to put the firs name it finds in all three spots. Does that make sense?

Thanks

Mike

 

[T. Valko]

I have a fairly simple index/ match situation where I have a range with names and then corresponding sales totals. I am trying to come up with a way to automatically sort and rank them accordingly. So the Large formula works great for putting the data in descending order, and my indexand match formula works great to match the name with the corresponding sales figure. What I can't figure out is how do I handle when two or more people have the same sales amount. My formula wants to put the firs name it finds in all three spots. Does that make sense?

Thanks

Mike

Are you doing a lookup on a rank?

Can you post some data so we can see what you have to work with?

 

[dugmic]

I believe I am trying to do a lookup on a rank.

The values formula right now is - =LARGE(allteams,1), =LARGE(allteams,2), etc. all the way down.
My associating name formula now is - =INDEX(test1,MATCH(N12,test2,0))

Hopefully that helps a little.

George 1925
Alex 1631
Taylor 1300
Sean 1045
Martin 950
Jon 915
Andrew 810
Kim 782.5
Brian 720
Brian 720
Jose 637.5
Colin 562.5
Andrea 550
Alejo 525
Alexandra 500
Krystal 420
Benjamin 390
Chanel 325
Mona 195
Diego 175
Diego 175
Joe 137.5
Fernando 125
Fernando 125
Sue 0
Sue 0


Sean $1,045.00 Names are in the range named Test1, values in range name test2
Brian $720.00
Martin $950.00
Colin $562.50
Kim $782.50
Sue $0.00
Jose $637.50
Andrew $810.00
Andrea $550.00
Alexandra $500.00
Fernando $125.00
Joe $137.50
George $1,925.00
Taylor $1,300.00
Alejo $525.00
Diego $175.00
John $175.00
Tammy $720.00
Jon $915.00
Alex $1,631.00
Conor $125.00
Mona $195.00
Ben $0.00
Krystal $420.00
Chanel $325.00
Benjamin $390.00

 

[T. Valko]

I believe I am trying to do a lookup on a rank.

The values formula right now is - =LARGE(allteams,1), =LARGE(allteams,2), etc. all the way down.
My associating name formula now is - =INDEX(test1,MATCH(N12,test2,0))

Hopefully that helps a little.

George 1925
Alex 1631
Taylor 1300
Sean 1045
Martin 950
Jon 915
Andrew 810
Kim 782.5
Brian 720
Brian 720
Jose 637.5
Colin 562.5
Andrea 550
Alejo 525
Alexandra 500
Krystal 420
Benjamin 390
Chanel 325
Mona 195
Diego 175
Diego 175
Joe 137.5
Fernando 125
Fernando 125
Sue 0
Sue 0


Sean $1,045.00 Names are in the range named Test1, values in range name test2
Brian $720.00
Martin $950.00
Colin $562.50
Kim $782.50
Sue $0.00
Jose $637.50
Andrew $810.00
Andrea $550.00
Alexandra $500.00
Fernando $125.00
Joe $137.50
George $1,925.00
Taylor $1,300.00
Alejo $525.00
Diego $175.00
John $175.00
Tammy $720.00
Jon $915.00
Alex $1,631.00
Conor $125.00
Mona $195.00
Ben $0.00
Krystal $420.00
Chanel $325.00
Benjamin $390.00

Try this...

Book1
	A	B	C	D	E	F
2	Sean	$1,045.00	4	_	George	$1,925.00
3	Brian	$720.00	9	_	Alex	$1,631.00
4	Martin	$950.00	5	_	Taylor	$1,300.00
5	Colin	$562.50	12	_	Sean	$1,045.00
6	Kim	$782.50	8	_	Martin	$950.00
7	Sue	$0.00	25	_	Jon	$915.00
8	Jose	$637.50	11	_	Andrew	$810.00
9	Andrew	$810.00	7	_	Kim	$782.50
10	Andrea	$550.00	13	_	Brian	$720.00
11	Alexandra	$500.00	15	_	Tammy	$720.00
12	Fernando	$125.00	23	_	Jose	$637.50
13	Joe	$137.50	22	_	Colin	$562.50
14	George	$1,925.00	1	_	Andrea	$550.00
15	Taylor	$1,300.00	3	_	Alejo	$525.00
16	Alejo	$525.00	14	_	Alexandra	$500.00
17	Diego	$175.00	20	_	Krystal	$420.00
18	John	$175.00	21	_	Benjamin	$390.00
19	Tammy	$720.00	10	_	Chanel	$325.00
20	Jon	$915.00	6	_	Mona	$195.00
21	Alex	$1,631.00	2	_	Diego	$175.00
22	Conor	$125.00	24	_	John	$175.00
23	Mona	$195.00	19	_	Joe	$137.50
24	Ben	$0.00	26	_	Fernando	$125.00
25	Krystal	$420.00	16	_	Conor	$125.00
26	Chanel	$325.00	18	_	Sue	$0.00
27	Benjamin	$390.00	17	_	Ben	$0.00
Sheet1

Enter this formula in C2 and copy down to the end of data. This will return a unique rank for each numeric value in column B.

=RANK(B2,B$2:B$27)+COUNTIF(B$2:B2,B2)-1

Enter this formula in E2:

=INDEX(A$2:A$27,MATCH(ROWS(E$2:E2),C$2:C$27,0))

Enter this formula in F2:

=INDEX(B$2:B$27,MATCH(E2,A$2:A$27,0))

Select both E2 and F2 then copy down to E27:F27.

 

[dugmic]

Absolutely Perfect. Thank you so much!!!

 

[T. Valko]

Absolutely Perfect. Thank you so much!!!

You're welcome. Thanks for the feedback!