Author: Oscar Cronquist Article last updated on August 29, 2017

Question: How do I  merge three columns 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

(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

(Excel 2007 Workbook *.xls)

Functions in this article

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function […]

Returns the number of rows in a reference or an array