Question: How do I concatenate two columns? To be more detailed, I have two named ranges (List1 and List2) that I want to merge, see picture below. How?

List1 has the defined name List1 (B2:B5).
List1 has the first cell (B2) defined name List1_start.

List2 has the defined name List2 (D3:D5).
List2 has the first cell (D3) defined name List2_start.

What are named ranges?

Answer: I created a new list List3 (F2:F13), with the start cell named List3_start (F2).

Here is the formula in F2:F13: =IF(ROWS(List1)>=ROW()-(ROW(List3_start)-1), OFFSET(List1_start, ROW(INDIRECT("1:"&ROWS(List1)))-1, 0), IF((ROWS(List1)+ROWS(List2))>=ROW()-(ROW(List3_start)-1), OFFSET(List2_start, SMALL(ROW(INDIRECT("1:"&ROWS(List2)))-1, ROW()-ROWS(List1)-1), 0), "")) + Ctrl + Shift + Enter

To make this formula work, you need to define names for your two lists. List1 and List2
You need to define start cells for your two lists. List1_start and List2_start
You need to define start cell for your new merged list. List3_start

Download excel sample file for this article.
merge-two-lists.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

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

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

SMALL(array,k) returns the k-th smallest row number in this data set.

Related posts:

1. Count unique values in two lists combined in excel