Merge three columns into one list in excel
Question: How do I merge three columns into one list?
Answer:
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 copied down to D13.
Named ranges
List1 (A2:A6)
List2 (B2:B3)
List3 (C2:C5)
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.
Download excel sample file for this tutorial.
merge-three-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
Related posts:
- Merge two columns into one list in excel
- How to filter values between 0.5 and 1.5 from two columns in excel 2007
- Identify missing values in two columns using excel formula
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Extract a list of duplicates from two columns combined using array formula in excel
- Extract a list of duplicates from three columns combined using array formula in excel
- Merge two lists
- Count unique distinct values in two columns with date criteria in excel
- Extract a list of duplicates from a column using array formula in excel



Leave a Reply