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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Firm[/TD]
[TD="align: center"]Volume[/TD]
[/TR]
[TR]
[TD]National Bank[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]CIBC World Markets[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]TD Securities[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]TD Securities[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]TD Securities[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Interactive Brokers[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]PI Financial[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]PI Financial[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]RBC Financial[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


The table should look like:

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Firm[/TD]
[TD="align: center"]Volume[/TD]
[/TR]
[TR]
[TD]National Bank[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]CIBC World Markets[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]TD Securities[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]TD Securities[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]TD Securities[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Interactive Brokers[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]PI Financial[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]PI Financial[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]RBC Financial[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Firm[/TD]
[TD="align: center"]Volume[/TD]
[/TR]
[TR]
[TD]National Bank[/TD]
[TD]12,000[/TD]
[/TR]
[TR]
[TD]CIBC World Markets[/TD]
[TD]10,000[/TD]
[/TR]
[TR]
[TD]TD Securities[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Morgan Stanley[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Scotia Capital[/TD]
[TD]5,000[/TD]
[/TR]
[TR]
[TD]Interactive Brokers[/TD]
[TD]2,000[/TD]
[/TR]
[TR]
[TD]PI Financial[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]Haywood Securities[/TD]
[TD]1,000[/TD]
[/TR]
[TR]
[TD]RBC Financial[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.