Combining arrays for a Lookup function
 

Is there a way to combine two named ranges, as if they were one, so it can
be used as an array argument in a lookup function? In other words, named
array-1 (A1:A5) combined with named array-2 (A10:A15) would become one
non-contiguous column (A1:A5, A10:A15) holding the continuous search values
for a LOOKUP function.

Combining arrays for a Lookup function
 

What would your lookup formula look like?

There is an add-in available that has a function that can do this but why
not just lookup as separate ranges?

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Is there a way to combine two named ranges, as if they were one, so it can
be used as an array argument in a lookup function? In other words, named
array-1 (A1:A5) combined with named array-2 (A10:A15) would become one
non-contiguous column (A1:A5, A10:A15) holding the continuous search
values for a LOOKUP function.

Combining arrays for a Lookup function
 

Hello Biff,

Thank you for the reply. We're not sure what the LOOKUP formula would look
like except that the Lookup_Vector (or Table_Array) arguments would be two
non-contiguous ranges interpreted as one range (as if pasted together
without the space in between). In other words, the formula would look down
column one, jumping over the voids, as if it were one contiguous column.

There could be many applications for this, but one example would be
extracting employee names from two different tables (actually visual work
assignment schedules) in order to combine them in a third table (an
inservice attendance register) as one group of alphabetized employees. We
can perform this task using separate ranges, but the resulting table would
be easier to manage if we could treat the two non-contiguous ranges as a
single range.

Thanks,
Richard

**************
"T. Valko" wrote in message
...
What would your lookup formula look like?

There is an add-in available that has a function that can do this but why
not just lookup as separate ranges?

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Is there a way to combine two named ranges, as if they were one, so it
can be used as an array argument in a lookup function? In other words,
named array-1 (A1:A5) combined with named array-2 (A10:A15) would become
one non-contiguous column (A1:A5, A10:A15) holding the continuous search
values for a LOOKUP function.

Combining arrays for a Lookup function
 

There is a free add-in called Morefunc.xll that has a function included with
it called ARRAY.JOIN that will do what you want.

Do a Google search on the add-in. Sometimes the authors website is fubar but
there are other sites where you can git it.

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Hello Biff,

Thank you for the reply. We're not sure what the LOOKUP formula would
look like except that the Lookup_Vector (or Table_Array) arguments would
be two non-contiguous ranges interpreted as one range (as if pasted
together without the space in between). In other words, the formula
would look down column one, jumping over the voids, as if it were one
contiguous column.

There could be many applications for this, but one example would be
extracting employee names from two different tables (actually visual work
assignment schedules) in order to combine them in a third table (an
inservice attendance register) as one group of alphabetized employees. We
can perform this task using separate ranges, but the resulting table would
be easier to manage if we could treat the two non-contiguous ranges as a
single range.

Thanks,
Richard

**************
"T. Valko" wrote in message
...
What would your lookup formula look like?

There is an add-in available that has a function that can do this but why
not just lookup as separate ranges?

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Is there a way to combine two named ranges, as if they were one, so it
can be used as an array argument in a lookup function? In other words,
named array-1 (A1:A5) combined with named array-2 (A10:A15) would become
one non-contiguous column (A1:A5, A10:A15) holding the continuous search
values for a LOOKUP function.

Combining arrays for a Lookup function
 

Thanks T. It looks like a very interesting add-in. I believe I found the
author's (Laurent Longre) site at
http://xcell05.free.fr/morefunc/english/index.htm but the download link
seems to be defective at he moment. We will try latter.

Thank You,
Richard

**************
"T. Valko" wrote in message
...
There is a free add-in called Morefunc.xll that has a function included
with it called ARRAY.JOIN that will do what you want.

Do a Google search on the add-in. Sometimes the authors website is fubar
but there are other sites where you can git it.

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Hello Biff,

Thank you for the reply. We're not sure what the LOOKUP formula would
look like except that the Lookup_Vector (or Table_Array) arguments would
be two non-contiguous ranges interpreted as one range (as if pasted
together without the space in between). In other words, the formula
would look down column one, jumping over the voids, as if it were one
contiguous column.

There could be many applications for this, but one example would be
extracting employee names from two different tables (actually visual work
assignment schedules) in order to combine them in a third table (an
inservice attendance register) as one group of alphabetized employees.
We can perform this task using separate ranges, but the resulting table
would be easier to manage if we could treat the two non-contiguous ranges
as a single range.

Thanks,
Richard

**************
"T. Valko" wrote in message
...
What would your lookup formula look like?

There is an add-in available that has a function that can do this but
why not just lookup as separate ranges?

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Is there a way to combine two named ranges, as if they were one, so it
can be used as an array argument in a lookup function? In other words,
named array-1 (A1:A5) combined with named array-2 (A10:A15) would
become one non-contiguous column (A1:A5, A10:A15) holding the
continuous search values for a LOOKUP function.

Combining arrays for a Lookup function
 

Here's an alternative download site:

http://www.download.com/Morefunc/300...-10423159.html

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Thanks T. It looks like a very interesting add-in. I believe I found the
author's (Laurent Longre) site at
http://xcell05.free.fr/morefunc/english/index.htm but the download link
seems to be defective at he moment. We will try latter.

Thank You,
Richard

**************
"T. Valko" wrote in message
...
There is a free add-in called Morefunc.xll that has a function included
with it called ARRAY.JOIN that will do what you want.

Do a Google search on the add-in. Sometimes the authors website is fubar
but there are other sites where you can git it.

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Hello Biff,

Thank you for the reply. We're not sure what the LOOKUP formula would
look like except that the Lookup_Vector (or Table_Array) arguments would
be two non-contiguous ranges interpreted as one range (as if pasted
together without the space in between). In other words, the formula
would look down column one, jumping over the voids, as if it were one
contiguous column.

There could be many applications for this, but one example would be
extracting employee names from two different tables (actually visual
work assignment schedules) in order to combine them in a third table (an
inservice attendance register) as one group of alphabetized employees.
We can perform this task using separate ranges, but the resulting table
would be easier to manage if we could treat the two non-contiguous
ranges as a single range.

Thanks,
Richard

**************
"T. Valko" wrote in message
...
What would your lookup formula look like?

There is an add-in available that has a function that can do this but
why not just lookup as separate ranges?

--
Biff
Microsoft Excel MVP


"Blue Max" wrote in message
...
Is there a way to combine two named ranges, as if they were one, so it
can be used as an array argument in a lookup function? In other
words, named array-1 (A1:A5) combined with named array-2 (A10:A15)
would become one non-contiguous column (A1:A5, A10:A15) holding the
continuous search values for a LOOKUP function.