# HOW TO LINK (CONCATENATE) TWO ARRAYS IN ONE ARRAY FORMULA?

#### ruiseixas

##### New Member
Hi,

I am having difficulty on this one: Suppose you have these 2 arrays, each one from one sheet:
{1, 3, 5} and {2, 4, 6}

and from the both arrays I want to extract the 1st or 2nd lowest elements into two different cells.

Like this:
Cell1 = 1
Cell2 = 2

Nothing complicated if some how we link the two arrays in to this one:
{1, 3, 5, 2, 4, 6}

Solution:
Cell1 -> {=SMALL ({1, 3, 5, 2, 4, 6}, 1)
Cell2 -> {=SMALL ({1, 3, 5, 2, 4, 6}, 2)

The plus sign (+) don't work and gives {3, 7, 11}, and that is not what I need.

Any one out there can figure out a way (array formulas) to make the following without macros?

LINK ({1, 3, 5}, {2, 4, 6}) = {1, 3, 5, 2, 4, 6}

Thanks folks.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### dcardno

##### Well-known Member
If both arrays are on the same sheet you can define a Named Range that includes both arrays: Insert | Name | Define - then ctrl-click to define as many ranges as you need. Alternatively, you can define the Named Range in VBA, or as a union of one or more dynamic ranges - but I think both of these are probably more than you need.

The Named Range can be used in the Small function, as:
Code:
``=small(DataRange, x)``
where x is any integer

#### barry houdini

##### MrExcel MVP

=SMALL(ARRAY.JOIN({1,3,5},{2,4,6}),2)

you can get morefunc here

http://xcell05.free.fr/

#### pgc01

##### MrExcel MVP
Hi ruiseixas
Welcome to the board

Suppose you have these 2 arrays, each one from one sheet .... and from the both arrays I want to extract the 1st or 2nd lowest elements into two different cells.

You can do it with just native excel functions using an auxilliary array to filter the elements from the 2 ranges.

Example: You want the second smallest value in ranges Sheet1!A1:A4 and Sheet2!A1:A3

In this case you use an auxilliary array of 7 elements to build dinamically a concatenated array of the 2 ranges, for the first 4 elements you choose the first range and for the other 3 the second range.

Code:
``=SMALL(IF(ROW(1:7)<=4,N(OFFSET(Sheet1!\$A\$1:\$A\$4,-1+ROW(1:7),0)),N(OFFSET(Sheet2!\$A\$1:\$A\$3,-1+ROW(1:7)-4,0))),2)``

Of course it is not very practical to determine in each case those hardcoded numbers and ranges.

A more elegant and general solution is

Define both ranges as named ranges Rng1 refers to Sheet1!A1:A4 and Rng2 refers to Sheet2!A1:A3

And now you can use the general formula:
Code:
``=SMALL(IF(ROW(INDIRECT("1:"&ROWS(Rng1)+ROWS(Rng2)))<=ROWS(Rng1),N(OFFSET(Rng1,-1+ROW(INDIRECT("1:"&ROWS(Rng1)+ROWS(Rng2))),0)),N(OFFSET(Rng2,-1+ROW(INDIRECT("1:"&ROWS(Rng1)+ROWS(Rng2)))-ROWS(Rng1),0))),2)``

In this formula you dont have to worry about the sizes of the ranges.

Hope this helps
PGC

#### ruiseixas

##### New Member
EASY SOLUTION

Hi guys,

after some thinking, I solve the problem this way:

we have these two arrays each one in one distinct sheet

Code:
``````Sheet1!A1:A3 = {1, 3, 5}
Sheet2!A1:A3 = {2, 4, 6}``````

and we want to get the 2nd element of the total amount of data using the SMALL function, so we do the following

Code:
``{=SMALL ({1, 0}*Sheet1!A1:A3 + {0, 1}*Sheet2!A1:A3, 2}``

Attention must be taken to the fact that the two or more arrays must be of the same size, in this particular case size 3.

Thanks again.

##### MrExcel MVP
Re: EASY SOLUTION

Hi guys,

after some thinking, I solve the problem this way:

we have these two arrays each one in one distinct sheet

Code:
``````Sheet1!A1:A3 = {1, 3, 5}
Sheet2!A1:A3 = {2, 4, 6}``````

and we want to get the 2nd element of the total amount of data using the SMALL function, so we do the following

Code:
``{=SMALL ({1, 0}*Sheet1!A1:A3 + {0, 1}*Sheet2!A1:A3, 2}``

Attention must be taken to the fact that the two or more arrays must be of the same size, in this particular case size 3.

Thanks again.

=SMALL(Sheet1:Sheet2!A1:A3,2)

would suffice.

#### ruiseixas

##### New Member
NOT REALLY

Hi guys,

after some thinking, I solve the problem this way:

we have these two arrays each one in one distinct sheet

Code:
``````Sheet1!A1:A3 = {1, 3, 5}
Sheet2!A1:A3 = {2, 4, 6}``````

and we want to get the 2nd element of the total amount of data using the SMALL function, so we do the following

Code:
``{=SMALL ({1, 0}*Sheet1!A1:A3 + {0, 1}*Sheet2!A1:A3, 2}``

Attention must be taken to the fact that the two or more arrays must be of the same size, in this particular case size 3.

Thanks again.

=SMALL(Sheet1:Sheet2!A1:A3,2)

would suffice.

It works, but only on the same cells for all sheets, and with the use of array formulas totally limited.
The formula that I write before its incorrect, I will give the correct one for the case of lines and rows:

LINES:
Code:
``{=SMALL ({1, 0}*Sheet1!A1:C1 + {0, 1}*Sheet2!A1:C1, 2)}``

ROWS:
Code:
``{=SMALL (Sheet1!A1:A3*{1\ 0} + Sheet2!A1:A3*{0\ 1}, 2)}``

Replies
24
Views
426
Replies
11
Views
254
Replies
1
Views
122
Replies
14
Views
224
Replies
4
Views
126

### Forum statistics

1,171,060
Messages
5,873,523
Members
432,982
Latest member
meepio ### 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.

### Which adblocker are you using?    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

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