# Lookup with disjointed ranges

#### wasabi

##### New Member
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
Well, lookup() require 2 criteria, and you only have 1... so that may be the issue?

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)``

Can you post some sample data and the desired result?

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

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).

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

Sorry for the delay, but I would just like to say that using your ArrayUnion function worked like a charm. Many thanks.

Sorry for the delay, but I would just like to say that using your ArrayUnion function worked like a charm. Many thanks.

You are welcome. Thanks for providing feedback.

Replies
6
Views
877
Replies
0
Views
290
Replies
2
Views
396
Replies
8
Views
706
Replies
2
Views
279

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?

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