Turn a split range into an array using defined names

**BLM** · 03-12-2011, 03:41 AM

I'd like to take a split ranges of data, for example, A1:A10 and C1:C10, and turn it into an array, preferably without using VBA. When I use defined names and define a formula as

=A1:A10

it represents that data as an array, and can be used like so in formulas using SMALL(), LARGE(), MATCH(), etc. that require an array. But when I try to define the name as

=A1:A10, C1:C10

it doesn't recognize the data as an array and as such gives me an error when I try to use it like one. Anybody have any ideas?

**DonkeyOte** · 03-12-2011, 04:04 AM

certain functions can handle non-contiguous ranges (SMALL, LARGE) whilst others can not (MATCH), eg:

Please Login or Register  to view this content.

would return the 2nd smallest value from A1:A10,C1:C10 - you could equally use your Defined Name.

For those functions that require vectors (MATCH for ex.) you can't use native functions to create a vector from non-contiguous ranges - you need VBA (or 3rd party add-in like morefunc.xll [see ARRAYJOIN])

**DonkeyOte** · 03-12-2011, 04:07 AM

FWIW, I created a thread on this type of issue previously to see if anyone could think of a way to do this...

http://www.excelforum.com/excel-work...us-ranges.html

End result is: not possible with native functions (from a practical perspective at any rate) ... INDEX is flexible just not quite flexible enough.