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.

This example merges two columns into one column using an array formula. If you are looking for merging two data lists with criteria, check this out: Merge lists with criteria

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