Hi All, I have a table with rank numbers. (B2:U25) Some ranks are the same within a column (so 2 rank 2's as in Column D - rows 14 and 19)
From the ranks, I lookup the value in column V.
In rows 28, 29 and 30 I use the lookup for the first 3 ranks.
I have attached a sheet as an example.
The problem is like in column D, I have 1 rank 1, 2 ranks 2's and 0 rank 3's. Using HLOOKUP it only finds the first rank 2. How can I also find the 2nd rank 2?
Maybe try this:
=INDEX($V$1:$V$25,SUM(IF(SMALL(B$2:B$25+ROW(B$2:B$25)/100,ROW(A1))=B$2:B$25+ROW(B$2:B$25)/100,ROW(B$2:B$25),0)))
as an array formula (confirm with ctrl+shift+enter) in B28 and then copy down/across
Edit: or maybe..
=INDEX($V$2:$V$25,MATCH(SMALL(B$2:B$25+ROW(A$2:A$25)/100,ROW(A1)),B$2:B$25+ROW(A$2:A$25)/100,0))
Last edited by ragulduy; 01-08-2014 at 07:52 AM.
The spreadsheet I opened contained no formulas so it is hard to know how you arrived at the rankings.
However, if your issue is trying to deal with ranking orders that contain a #1, a couple #2s etc, there is a way to get rid of dups in rankings. You use the standard rank formula so lets say you were ranking column D, the formula would look like this... =RANK(D2,$D$2:$D$25,1)+COUNTIF($D$2:D2,D2)-1.
For what it's worth.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks