Lookup with disjointed ranges

wasabi

New Member
Joined
Apr 12, 2010
Messages
28
Is there a way to use lookup to search within more than one range (i.e. "1:1" and "5:5") at the same time? I've tried declaring a named range containing both ranges but, while functions such as SUM() work just fine in such a split range, LOOKUP() returns an error.

I know I could use nested IFERROR's:
Code:
IFERROR(LOOKUP(range1),LOOKUP(range2))
but I'd like to know if there is a better solution which doesn't have the limitation on the number of nested IF's.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
That was pseudocode, sorry.

And it actually takes three: the search value, the search vector and the result vector. So the code with nested if's would be

Code:
IFERROR(LOOKUP(value,search1,result1),LOOKUP(value,search2,result2))

Well, that's with one if, but I'd nest another one if I had another combination of search/result vectors.

However, is there a way of doing (inventing incorrect syntax here...):
Code:
IFERROR(LOOKUP(value,search1+search2,result1+result2)
 
Upvote 0
That was pseudocode, sorry.

And it actually takes three: the search value, the search vector and the result vector. So the code with nested if's would be

Code:
IFERROR(LOOKUP(value,search1,result1),LOOKUP(value,search2,result2))

Well, that's with one if, but I'd nest another one if I had another combination of search/result vectors.

However, is there a way of doing (inventing incorrect syntax here...):
Code:
IFERROR(LOOKUP(value,search1+search2,result1+result2)

Care to be specific? A formulation this abstract does not provide enough information...
 
Upvote 0
Let's say I have a table with the following values (excuse me, but I don't know how to format tables in the forum).

1 | 2 | 3 | 4 | 5 (using the pseudocode above, lets call this search1)
a | b | c | d | e (result1)

6 | 7 | 8 | 9 (search2)
f | g | h | i (result2)

I would like to input a number value between 1 and 9 and get the corresponding letter (i.e. I search for 8 and the result is "h"). Obviously my actual problem is much more complex than this, but the problem is effectively this. However, since I do not know a priori whether the number to be called is in the first or second line of search values, I need to search both in one call (or in multiple calls using nested IF's).
 
Upvote 0
Let's say I have a table with the following values (excuse me, but I don't know how to format tables in the forum).

1 | 2 | 3 | 4 | 5 (using the pseudocode above, lets call this search1)
a | b | c | d | e (result1)

6 | 7 | 8 | 9 (search2)
f | g | h | i (result2)

I would like to input a number value between 1 and 9 and get the corresponding letter (i.e. I search for 8 and the result is "h"). Obviously my actual problem is much more complex than this, but the problem is effectively this. However, since I do not know a priori whether the number to be called is in the first or second line of search values, I need to search both in one call (or in multiple calls using nested IF's).

Some options...

[1]
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},LOOKUP(8,search1,result1),LOOKUP(8,search2,result2)))

[2]
=LOOKUP(8,ARRAYUNION(search1,search2),ARRAYUNION(result1,result2))

ARRAYUNION is a function in VBA...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: http://makeashorterlink.com/?P20022174
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

which you need to add to your workbook in order to use it.
 
Upvote 0
Sorry for the delay, but I would just like to say that using your ArrayUnion function worked like a charm. Many thanks.
 
Upvote 0

Forum statistics

Threads
1,211,962
Messages
6,105,105
Members
447,947
Latest member
OX_2005

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top