Lookup with disjointed ranges

[wasabi]

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:

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.

 

[pplstuff]

Well, lookup() require 2 criteria, and you only have 1... so that may be the issue?

 

[wasabi]

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

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

IFERROR(LOOKUP(value,search1+search2,result1+result2)

 

[pplstuff]

Can you post some sample data and the desired result?

 

[Aladin Akyurek]

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

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

IFERROR(LOOKUP(value,search1+search2,result1+result2)

Care to be specific? A formulation this abstract does not provide enough information...

 

[wasabi]

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

 

[Aladin Akyurek]

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.

 

[wasabi]

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

 

[Aladin Akyurek]

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.