Question: How do I  merge two ranges into one list?

Answer:

merge-two-named-ranges-into-one-list

Excel 2007 array formula in C2:

=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), "")) + ENTER. Copy cell C2 and paste it down as far as needed.

Earlier versions of excel, array formula in C2:

=IF(ISERROR(INDEX(List1, ROWS($C$1:C1))), IF(ISERROR(INDEX(List2, ROWS($C$1:C1)-ROWS(List1))), "", INDEX(List2, ROWS($C$1:C1)-ROWS(List1))), INDEX(List1, ROWS($C$1:C1))) + ENTER. Copy cell C2 and paste it down as far as needed.

Named ranges

List1 (A2:A6)
List2 (B2:B3)
What is named ranges?

How to create dynamic named ranges

Excel 2007/2010 users: Convert the ranges to excel tables and update formula references accordingly.

Previous excel versions:

List1: =OFFSET(Sheet1!$B$3, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)

List2: =OFFSET(Sheet1!$C$3, 0, 0, COUNTA(Sheet1!$C:$C)-1, 1)

How to implement array formula to your workbook

Change named ranges. If your merged list starts at, for example, F3. Change C1:$C$1 in the above formula to F2:$F$2.

How Excel 2007 array formula in C2 works

Step 1 - Extracting List 1

=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), ""))

In cell C2: INDEX(List1, ROWS(C1:$C$1))

becomes

INDEX(List1, 1) equals "AA"

In cell C3: INDEX(List1, ROWS(C1:$C$1))

becomes

INDEX(List1, ROWS(C2:$C$1))

becomes

INDEX(List1, 2) equals "DD"

and so on...

Step 2 - Error when all values in List 1 are processed

=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), ""))

In cell C7 something unexpected happens:

In cell C7: INDEX(List1, ROWS(C1:$C$1))

becomes

INDEX(List1, ROWS(C6:$C$1))

becomes

INDEX(List1, 6) equals "#REF!" error

There are no more values in List 1 so we need to continue on List 2.

IFERROR() takes care of this:

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

Step 3 - Continue with List 2

In cell C7:

=IFERROR(INDEX(List1, ROWS(C6:$C$1)), IFERROR(INDEX(List2, ROWS(C6:$C$1)-ROWS(List1)), ""))

becomes

=IFERROR(#REF!, IFERROR(INDEX(List2, ROWS(C6:$C$1)-ROWS(List1)), ""))

and becomes

IFERROR(INDEX(List2, ROWS(C6:$C$1)-ROWS(List1)), "")

INDEX(List2, ROWS(C6:$C$1)-ROWS(List1))

becomes

INDEX(List2, 6-ROWS(List1))

becomes

INDEX(List2, 6-5)

becomes

INDEX(List2, 1) equals "MM" in List 2

and so on...

Step 4 - Error when all values in List 2 and List 1 are processed

In cell C9:

=IFERROR(INDEX(List1, ROWS(C8:$C$1)), IFERROR(INDEX(List2, ROWS(C8:$C$1)-ROWS(List1)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX(List2, ROWS(C8:$C$1)-ROWS(List1)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX(List2, 8-ROWS(List1)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX(List2, 8-5), ""))

becomes

=IFERROR(REF!, IFERROR(REF!, "")) equals "" (nothing)

Download excel sample file for this tutorial.
merge-two-columns2.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROWS(array) returns the number of rows in a reference or an array

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE