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 copied down to D13.

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

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 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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Merge two columns into one list in excel
  2. How to filter values between 0.5 and 1.5 from two columns in excel 2007
  3. Identify missing values in two columns using excel formula
  4. Extract a unique distinct list from two columns using excel 2007 array formula
  5. Extract a unique distinct list from three columns in excel
  6. Extract a list of duplicates from two columns combined using array formula in excel
  7. Extract a list of duplicates from three columns combined using array formula in excel
  8. Merge two lists
  9. Count unique distinct values in two columns with date criteria in excel
  10. Extract a list of duplicates from a column using array formula in excel