Question: How do I merge two ranges into one list?

Answer:

merge-two-named-ranges-into-one-list

Formula in C2:

=IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), ""))

Copy cell C2 and paste it down as far as needed.

This example merges two columns into one column using an array formula. If you are looking for merging two data lists with criteria, check this out:

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

If there are blanks in your lists the following article shows you a workaround:

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

Perhaps you have three different lists, read this post:

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

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

Earlier versions of excel, formula in C2:

=IF(ISERROR(INDEX($A$2:$A$6, ROWS($C$1:C1))), IF(ISERROR(INDEX($B$2:$B$3, ROWS($C$1:C1)-ROWS($A$2:$A$6))), "", INDEX($B$2:$B$3, ROWS($C$1:C1)-ROWS($A$2:$A$6))), INDEX($A$2:$A$6, ROWS($C$1:C1)))

Copy cell C2 and paste it down as far as needed.

How to create dynamic named ranges

Excel 2007 users (and later versions): Convert the lists to excel defined tables and update formula references accordingly. The following article tells you all about excel defined tables:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Comments(0) Filed in category: Excel, Excel table

Previous Excel versions:

List1: =OFFSET(Sheet1!$B$3, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)

List2: =OFFSET(Sheet1!$C$3, 0, 0, COUNTA(Sheet1!$C:$C)-1, 1)

Learn more about dynamic named ranges:

Create a dynamic named range in excel

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns […]

Comments(12) Filed in category: Excel, Named range

How Excel 2007 array formula in C2 works

Step 1 - Extracting List 1

=IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), ""))

In cell C2: INDEX($A$2:$A$6, ROWS(C1:$C$1))

becomes

INDEX($A$2:$A$6, 1) equals "AA"

In cell C3: INDEX($A$2:$A$6, ROWS(C1:$C$1))

becomes

INDEX($A$2:$A$6, ROWS(C2:$C$1))

becomes

INDEX($A$2:$A$6, 2) equals "DD"

and so on...

INDEX function explained

Fetch a value in a data set based on coordinates.

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

Step 2 - Error when all values in List 1 are processed

=IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), ""))

In cell C7 something unexpected happens:

In cell C7: INDEX($A$2:$A$6, ROWS(C1:$C$1))

becomes

INDEX($A$2:$A$6, ROWS(C6:$C$1))

becomes

INDEX($A$2:$A$6, 6) equals "#REF!" error

There are no more values in List 1 so we need to continue on List 2.

IFERROR() takes care of this:

IFERROR(value, value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

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

Step 3 - Continue with List 2

In cell C7:

=IFERROR(INDEX($A$2:$A$6, ROWS(C6:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(#REF!, IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), ""))

and becomes

IFERROR(INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6)), "")

INDEX($B$2:$B$3, ROWS(C6:$C$1)-ROWS($A$2:$A$6))

becomes

INDEX($B$2:$B$3, 6-ROWS($A$2:$A$6))

becomes

INDEX($B$2:$B$3, 6-5)

becomes

INDEX($B$2:$B$3, 1) equals "MM" in List 2

and so on...

Step 4 - Error when all values in List 2 and List 1 are processed

In cell C9:

=IFERROR(INDEX($A$2:$A$6, ROWS(C8:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C8:$C$1)-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, ROWS(C8:$C$1)-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, 8-ROWS($A$2:$A$6)), ""))

becomes

=IFERROR(REF!, IFERROR(INDEX($B$2:$B$3, 8-5), ""))

becomes

=IFERROR(REF!, IFERROR(REF!, "")) equals "" (nothing)

Download excel sample file for this tutorial.

merge-two-columns2.xlsx
(Excel 2007 Workbook *.xlsx)

 

Recommended articles

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

Merge matching rows in excel

Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]

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