# Thread: Non-continuous range in hlookup (Excel 2003)

1. ## 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. ## 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>

3. ## 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. ## 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. ## 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. ## 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
•