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:

Excel udf: Combine cell ranges into a single range while eliminating blanks

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

Comments(4) Filed in category: Combine/Merge, Excel

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 lists with criteria

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

Comments(7) Filed in category: Combine/Merge, Excel

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 […]

Comments(28) Filed in category: Combine/Merge, Excel

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 […]

Comments(79) Filed in category: Excel, Unique distinct values

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 […]

Comments(13) Filed in category: Excel, Sort values

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.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

IFERROR function

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

Comments(0) Filed in category: Excel

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