VLookup without duplicates

[colinheslop1984]

Hello

I have a range of data set out in a table like below

I want to rank rank the stores using positions moved. Sorting the data by that column and expanding the selection doesn't seem to be working properly no matter what I try. So, I have done the following:

=SUM(LARGE(D$25:D$82,A84)) to order the data based on numbers highest to lowest for positions moved.

Then I thought I could do =VLOOKUP(D84,CHOOSE({1,2},D$25:D$82,B$25:B$82),2,0) to look up the name of the store using the positions moved as the criteria.

The problem is that some stores might have moved the same number of positions, therefore the lookup duplicates a store name.

How do I get around this?

RANK	STORE	RANK LY	POSITIONS MOVED
1	ABC	1	0
2	DEF	4	2
3	GHI	5	2
4	JKL	3	-1
5	MNO	9	4

 

[Eric W]

I couldn't quite match this formula to your sheet, since your example doesn't have the row/column headers on it. But you should be able to adapt this formula to work on your sheet:

Book1
	A	B	C	D	E	F	G	H
1	RANK	STORE	RANK LY	POSITIONS MOVED			Positions Moved	Store
2	1	ABC	1	0			4	MNO
3	2	DEF	4	2			-1	JKL
4	3	GHI	5	2			2	DEF
5	4	JKL	3	-1			2	GHI
6	5	MNO	9	4			0	ABC
Sheet3

Cell Formulas
Range	Formula
H2:H6	H2	=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$6)/($D$2:$D$6=G2),COUNTIF($G$2:$G2,G2)))

 

[colinheslop1984]

That done the job perfectly, thank you

 

[Eric W]

Happy to help.