Question: How do I concatenate two columns? To be more detailed, I have two named ranges (List1 and List2) that I want to merge, see picture below. How?

merge-two-lists

List1 has the defined name List1 (B2:B5).
List1 has the first cell (B2) defined name List1_start.

List2 has the defined name List2 (D3:D5).
List2 has the first cell (D3) defined name List2_start.

What are named ranges?

Answer: I created a new list List3 (F2:F13), with the start cell named List3_start (F2).

merge-two-lists_2

Here is the formula in F2:F13: =IF(ROWS(List1)>=ROW()-(ROW(List3_start)-1), OFFSET(List1_start, ROW(INDIRECT("1:"&ROWS(List1)))-1, 0), IF((ROWS(List1)+ROWS(List2))>=ROW()-(ROW(List3_start)-1), OFFSET(List2_start, SMALL(ROW(INDIRECT("1:"&ROWS(List2)))-1, ROW()-ROWS(List1)-1), 0), "")) + Ctrl + Shift + Enter

To make this formula work, you need to define names for your two lists. List1 and List2
You need to define start cells for your two lists. List1_start and List2_start
You need to define start cell for your new merged list. List3_start

Download excel sample file for this article.
merge-two-lists.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

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

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

SMALL(array,k) returns the k-th smallest row number in this data set.


Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Merge two columns into one list in excel
  2. Merge three columns into one list in excel
  3. Count unique values in two lists combined in excel
  4. Highlight common values in two lists using conditional formatting in excel
  5. Lookup between two lists of data to highlight missing data using conditional formatting in excel
  6. Create unique list from two columns
  7. Identify missing values in two columns using excel formula
  8. Max or min out of two columns
  9. Extract largest values from two columns using array formula in excel
  10. Invert a list using named ranges