Vlookup unable to return the next cell that meets criteria

[A_Filippo]

I'm trying to build a table that lists a broker's volume from largest to smallest (top 10 firms only). If there are only 9 values in the data set, a zero will appear in the row.

To grab the volume, from largest to smallest, I'm using =LARGE($B$2:$B$63,1), =LARGE($B$2:$B$63,2), =LARGE($B$2:$B$63,3), ... =LARGE($B$2:$B$63,10). Then I'm doing a Vlookup to grab the corresponding firm. The issue arises when there is a duplicate volume number, it will grab the first firm in the data set multiple times instead of listing the second or third firm. How do I get excel to look for the next corresponding firm and IF there is a 0 in the volume row, to return a blank.

Firm	Volume
National Bank	12,000
CIBC World Markets	10,000
TD Securities	5,000
TD Securities	5,000
TD Securities	5,000
Interactive Brokers	2,000
PI Financial	1,000
PI Financial	1,000
RBC Financial	500
	0

<tbody>
</tbody>

The table should look like:

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Firm	Volume
National Bank	12,000
CIBC World Markets	10,000
TD Securities	5,000
TD Securities	5,000
TD Securities	5,000
Interactive Brokers	2,000
PI Financial	1,000
PI Financial	1,000
RBC Financial	500

<tbody>
</tbody>

Firm	Volume
National Bank	12,000
CIBC World Markets	10,000
TD Securities	5,000
Morgan Stanley	5,000
Scotia Capital	5,000
Interactive Brokers	2,000
PI Financial	1,000
Haywood Securities	1,000
RBC Financial	500
	0

<tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

How about:

Book1
	A	B	C	D	E	F
1	Firm	Volume			Firm	Volume
2	CIBC World Markets	10000			National Bank	12,000
3	Scotia Capital	5000			CIBC World Markets	10,000
4	PI Financial	1000			Scotia Capital	5,000
5	TD Securities	5000			TD Securities	5,000
6	Interactive Brokers	2000			Morgan Stanley	5,000
7	Haywood Securities	1000			Interactive Brokers	2,000
8	National Bank	12000			PI Financial	1,000
9	Morgan Stanley	5000			Haywood Securities	1,000
10	RBC Financial	500			RBC Financial	500
11
Sheet4

Cell Formulas
Range	Formula
E2	=IF(F2<>"",INDEX($A:$A,AGGREGATE(15,6,ROW($B$2:$B$63)/($B$2:$B$63=F2),COUNTIF($F$2:$F2,F2))),"")
F2	=IFERROR(LARGE($B$2:$B$63,ROWS($F$2:$F2)),"")

 

[A_Filippo]

That worked, amazing Eric. Spent many hours on Youtube and on here trying to piece this together. Enjoy your weekend and thanks again.

 

[Eric W]

Glad it worked for you. Thanks for the feedback.

Have a good weekend yourself.