+ Reply to Thread
Results 1 to 3 of 3

HLOOKUP 2nd instance

  1. #1
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    689

    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?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    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))
    Last edited by ragulduy; 01-08-2014 at 07:52 AM.

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,774

    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.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. HLOOKUP Help. HLOOKUP links to Drop down list problems
    By finalmike in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2013, 07:08 PM
  2. First and Last instance of a Value
    By Grimace in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2011, 02:10 AM
  3. HLOOKUP in HLOOKUP, Base Estimate Table in Excel
    By gqdallas in forum Excel General
    Replies: 20
    Last Post: 10-19-2009, 01:41 PM
  4. xl instance
    By tom taol in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2005, 01:06 PM

Bookmarks

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