Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    HLOOKP (Excel 2003)

    Hi Excel Experts,

    I am looking at a simple HLOOKUP formula. I have the % in Col A, and the ranges in Col D.

    The formula should look for A2 and check which range it falls in D2 and give the result in B2

    Attached is the file which is self explanatory.

    Regards
    Baiju

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HLOOKP (Excel 2003)

    This is not a HLOOKUP problem - HLOOKUP searches through a horizontally oriented list (a row); you have a vertical list (column).
    Your attachment is *not* self-explanatory. Could you give an example of what you expect the result to be?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: HLOOKP (Excel 2003)

    Hi Hans,

    I Want the range within which the A2 falls.

    I have entered the desired results in B2 based on the range in D2, can you give me a formula that will do that for me.

    Attached is the excel file, hope it will be self explanatory now.

    Regards
    Baiju

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: HLOOKP (Excel 2003)

    I think you want:
    =INDEX($D$2:$D$11,MATCH(A2,$D$2:$D$11,-1))

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HLOOKP (Excel 2003)

    Thanks, that makes it much clearer. Try this formula in B2:
    <code>
    =INDEX($D$2:$D$11,MATCH(A2,$D$2:$D$11,-1))
    </code>
    and fill down to B10.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: HLOOKP (Excel 2003)

    Hi Hans,

    Thanks its working perfectly.. thanx a ton.

    Rgds
    Baiju

  7. #7
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: HLOOKP (Excel 2003)

    Hi Rory,

    Thanks your formula also works great.

    Rgds
    Baiju

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: HLOOKP (Excel 2003)

    I would hope so - the two are exactly the same! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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