+ Reply to Thread
Results 1 to 5 of 5

Hlookup and Index match?

  1. #1
    Registered User
    Join Date
    05-31-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    88

    Hlookup and Index match?

    I'm trying to do an Hlookup but it still doesn't return the data that I need.

    Please help...

    Attached is my sample file and the output that I need.

    Thanks a million!
    Attached Files Attached Files
    Last edited by geng; 12-05-2010 at 11:20 PM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Hlookup and Index match?

    Geng, I see your data and your output.. but WHY is that your output? What logic are you using to pick those values from your data?

  3. #3
    Registered User
    Join Date
    05-31-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Hlookup and Index match?

    the logic is to return the last number > 0.

    Sample for PQR (A25) the last number that is >0 is 850(D7).

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Hlookup and Index match?

    Well, the following formula will do what you're after, but in my triptophan-induced state, I can't help but feel there's a much simpler index/match formula I'm overlooking.. sheesh.

    =INDEX(INDIRECT("B"&MATCH(A24,$A$2:$A$20,0)+1&":N"&MATCH(A24,$A$2:$A$20,0)+1),MATCH(2,1/(INDIRECT("B"&MATCH(A24,$A$2:$A$20,0)+1&":N"&MATCH(A24,$A$2:$A$20,0)+1)>0)))

    After typing or editing this formula you must press CTRL+SHIFT+ENTER, not just ENTER. When done properly Excel will automatically insert braces, { }, around your formula.

  5. #5
    Registered User
    Join Date
    05-31-2010
    Location
    Manila
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Hlookup and Index match?

    Thanks Paul!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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