Merge two columns into one list in excel
Question: How do I merge two ranges into one list?
Answer:
Excel 2007 array formula in C2:
=IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), "")) + CTRL + SHIFT + ENTER copied down.
Earlier versions of excel, array formula in C2:
=IF(ISERROR(INDEX(List1, ROWS($C$1:C1))), IF(ISERROR(INDEX(List2, ROWS($C$1:C1)-ROWS(List1))), "", INDEX(List2, ROWS($C$1:C1)-ROWS(List1))), INDEX(List1, ROWS($C$1:C1)))
Named ranges
List1 (A2:A6)
List2 (B2:B3)
What is named ranges?
How to implement array formula to your workbook
Change named ranges. If your merged list starts at, for example, F3. Change C1:$C$1 in the above formula to F2:$F$2.
Download excel sample file for this tutorial.
merge-two-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
ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE
Related posts:
- Merge three columns into one list in excel
- How to filter values between 0.5 and 1.5 from two columns in excel 2007
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a list of duplicates from two columns combined using array formula in excel
- Identify missing values in two columns using excel formula
- Extract a unique distinct list from three columns 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
- Create unique list from two columns



Leave a Reply