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

Answer:

Excel 2007 array formula in C2:

=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), "")) + CTRL + SHIFT + ENTER copied down.

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)))

Named ranges

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

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-columns.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

Related posts:

1. Merge two columns with possible blank cells in excel (formula)
2. Count matching cell values in two columns in excel
3. Validate unique list in excel
4. Count unique values in two lists combined in excel
5. Identify missing three character alpha code numbers in excel
6. Filter a column and create a new unique list sorted from A to Z using array formula in excel
7. Populate cells dynamically in a weekly schedule in excel