Combining arrays for a Lookup function

[Blue Max]

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.

 

[T. Valko]

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?

 

[Blue Max]

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]

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.

 

[Blue Max]

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]

Here's an alternative download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html