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.

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 three columns into one list in excel
2. How to filter values between 0.5 and 1.5 from two columns in excel 2007
3. Extract a unique distinct list from two columns using excel 2007 array formula
4. Extract a list of duplicates from two columns combined using array formula in excel
5. Identify missing values in two columns using excel formula
6. Extract a unique distinct list from three columns in excel
7. Extract a list of duplicates from three columns combined using array formula in excel
8. Merge two lists
9. Count unique distinct values in two columns with date criteria in excel
10. Create unique list from two columns