# HLOOKUP 2nd instance

1. ## HLOOKUP 2nd instance

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?

2. ## Re: HLOOKUP 2nd instance

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))

3. ## Re: HLOOKUP 2nd instance

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1