Ordered frequency

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to select the first X number of times that a value appears in a column (in order from the top down) and then take a value in the cell next to it? Sort of a vlookup in top down order, but only for the specified number of times.

For example, I have a list of stock symbols with their industry codes in the column next to them. I want Excel to search through the list of codes and provide the stock symbols that correspond to each industry code, but only the first X times that that code is encountered, when searching from the top down.
 
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100="industry_code";ROW($A$1:$A100)
),ROW(1:1)))

and copy down for as many symbols you need
 
Thanks for your response. I've read up on Index and Small, but unfortunately I don't quite get how this would work. The industry codes may be repeated several times within their column and while they are numbers, the size of the number can't be used for ranking -- only it's position. Also, the data in the other column is text.

Any further guidance would be appreciated, thanks.
 
Hi
have you tried the formula. It should work as SMALL is used to get the
kth smallest ROW if a match for your industry code is found. Simply try
it (and don't forget to enter this formula as array formula)
 
Is there a "then" statement in the if/then formula? How does the semicolon work? What should "industry_code" be replaced with?

Sorry for so many questions, I'm having trouble even figuring out how to try your suggestion.

Thanks again
 
Hi
for 'industry_code' insert the value you're looking for or a cell
reference which contain this value.
The 'then statement is
ROW($A$1:$A100)

But I made one mistake. Please replace the semicolons with comas. So
use
=INDEX($B$1:$B$100,SMALL(IF($A$1:$A$100="industry_code",ROW($A$1:$A100)
),ROW(1:1)))
 
Back
Top