Question: How do I  merge three columns into one list?

Answer:

merge-three-named-ranges-into-one-list

Excel 2007 array formula in D2:

=IFERROR(INDEX(List1, ROWS(D1:$D$1)), IFERROR(INDEX(List2, ROWS(D1:$D$1)-ROWS(List1)), IFERROR(INDEX(List3, ROWS(D1:$D$1)-ROWS(List1)-ROWS(List2)), ""))) + CTRL + SHIFT + ENTER

Recommended article:

Combine cell ranges eliminating blanks

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]

This is an array formula, here is how to enter it. Type the formula in cell D2, press and hold CTRL + SHIFT simultaneously. Press Enter once. Release all keys. If you did it correctly, you now have curly brackets before and after the formula.

Copy cell D2 and paste it to D3:D12.

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

Merge tables based on a condition

I have written a post about merging two single columns or ranges before: Merge two columns with possible blank cells. It demonstrates […]

Excel 2003 and earlier versions in cell D2:

=INDEX((List1, List2, List3), ROW(A1)*(ROW(A1)<=ROWS(List1))+(ROW(A1)-ROWS(List1))*(ROW(A1)>ROWS(List1))*(ROW(A1)<=(ROWS(List1)+ROWS(List2)))+(ROW(A1)-ROWS(List1)-ROWS(List2))*(ROW(A1)>ROWS(List1))*(ROW(A1)>(ROWS(List1)+ROWS(List2)))*(ROW(A1)<=(ROWS(List1)+ROWS(List2)+ROWS(List3))), , (ROW(A1)<=ROWS(List1))*1+(ROW(A1)>ROWS(List1))*(ROW(A1)<=(ROWS(List1)+ROWS(List2)))*2+(ROW(A1)>ROWS(List1))*(ROW(A1)>(ROWS(List1)+ROWS(List2)))*(ROW(A1)<=(ROWS(List1)+ROWS(List2)+ROWS(List3)))*3) + ENTER

Named ranges
List1 (A2:A6)
List2 (B2:B3)
List3 (C2:C5)

Recommended article:

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]

How to implement array formula to your workbook
Change named ranges. If your merged list starts at, for example, F3. Change D1:$D$1 in the above formula to F2:$F$2.

Recommended reading:

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Download excel file

merge-three-columns.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended reading:

Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

Download excel file

merge-three-columns_excel_2003.xls
(Excel 2007 Workbook *.xls)

Functions in this article

INDEX function explained

Fetch a value in a data set based on coordinates.

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

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