Non-continuous range in hlookup (Excel 2003)

Topic

New Reply

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…

Reply | Quote

Replies

You could use something like this:

=IF(ISERROR(HLOOKUP(B8,Range2,2,0)),IF(ISERROR(HLOOKUP(B8,Range3,2,0)),HLOOKUP(B8,Range4,2,0),HLOOKUP(B8,Range3,2,0)),HLOOKUP(B8,Range2,2,0))

with these range definitions:

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

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

=A8*10

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

You could use something like this:

=IF(ISERROR(HLOOKUP(B8,Range2,2,0)),IF(ISERROR(HLOOKUP(B8,Range3,2,0)),HLOOKUP(B8,Range4,2,0),HLOOKUP(B8,Range3,2,0)),HLOOKUP(B8,Range2,2,0))

with these range definitions:

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

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

=A8*10

Reply | Quote