VLOOKUP when there are duplicate values in table_array

[dan_dan_noodles]

I have looked at other threads dealing with similar issues and frankly couldn't figure out how to apply them to my situation. Please let me know if you can help.

Situation:
- I get daily volume data from our store locations
- I need to generate a report that ranks our stores in descending order by volume
- I need to use formulas for this (no manual sorting, pivot); the report needs to be automatically done when daily data is added to the data sheet

Problem:

- I use the LARGE function to rank the Volume, then use vlookup to find the Store # for that volume
- I don't know how to modify the vlookup function when 2 (or more) stores have the same volume. See example below.

Thanks.

==

Daily Volume Data

(A) (B) (C)
Volume Store # Store Name
$202 101 Red Bluff
$225 102 Porterville
$202 103 Loveland
$376 104 Mt Pleasant
$53 105 Mt. Crawford


Rank by Volume

(A) (B) (C)
Ranking Volume Store #
1 $376 104
2 $225 102
3 $202 101 (duplicate)
4 $202 101 (duplicate)
5 $53 105

 

[Matt Rogers]

Hi.

Maybe like this ...?

Excel Workbook
	A	B	C	D	E	F	G	H
1	Volume	Store	Shop-Name		Ranking	Volume	Store	Shop-Name
2	$202	101	Red Bluff		1	$376	104	Mt Pleasant
3	$225	102	Porterville		2	$225	102	Porterville
4	$202	103	Loveland		3	$202	101	Red Bluff
5	$376	104	Mt Pleasant		3	$202	103	Loveland
6	$53	105	Mt Crawford		5	$53	105	Mt Crawford
Sheet