Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Non-continuous range in hlookup (Excel 2003)

    I have attached a sample file that illustrates the issue. Basically, I have a user who wants to create an hlookup formula, but the lookup range is noncontiguous, which returns an error. The user wants to know if there is a way around this. I tried the old INDEX-MATCH approach, but that didn't work either. It seems to me that Excel's lookup and match formulas require a contiguous range. Am I missing something?

    Once again, I turn to the pros...

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-continuous range in hlookup (Excel 2003)

    You could use something like this:

    <pre>=IF(ISERROR(HLOOKUP(B8,Range2,2,0)),IF(ISERRO R(HLOOKUP(B8,Range3,2,0)),HLOOKUP(B8,Range4,2,0),H LOOKUP(B8,Range3,2,0)),HLOOKUP(B8,Range2,2,0))
    </pre>


    with these range definitions:

    <pre>Range2=Sheet1!$B$2:$D$5
    Range3=Sheet1!$F$2:$H$5
    Range4=Sheet1!$J$2:$L$5
    </pre>


    Of course, if that is the real table, wouldn't this be easier:

    <pre>=A8*10
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-continuous range in hlookup (Excel 2003)

    Very clever. Yet another simple and elegant approach. Thank you for the insight.

    Unfortunately the table in the example file was just for illustration. The real table(s) are more complex.

    Thanks again...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Non-continuous range in hlookup (Excel 2003)

    You could just use:
    =HLOOKUP(B8,Range3,2,0)

    Where range3 refersto:
    =Sheet1!$B$2:$L$5

    which is the contiguous range covered by range2. Since it is an exact match, the blank columns do not matter.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Non-continuous range in hlookup (Excel 2003)

    Actually I had thought of that in the first place, but the user said that this approach wouldn't be practical for him, for whatever reason. I couldn't find any functions that would work in such a situation and I got to thinking that maybe I'm just missing something, that perhaps there is a function that would work in such a situation. It looks like Legare's approach is the only one that would work in this particular situation.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Non-continuous range in hlookup (Excel 2003)

    You might want to get whay it is not "practical" and we can find ohter solutions. A custom function might a good way to go.

    Steve

Posting Permissions

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