How to merge columns using formulas

by | Apr 20, 2018 | Excel Tips & Tricks

How to merge columns using formulas

Traditional Copy+Paste might do all you want, but when working with large tables that are constantly updated, it's a tedious task to merge columns of data. Using this formula combination instead will save you time and prevent errors.

Syntax

=IFERROR(

     INDEX(first list, ROWS(expanding range from 1st row)),

     IFERROR(

          INDEX(second list, ROWS(expanding range from 1st row) - ROWS(first list)),""))

Steps

  1. We need to wrap the entire formula set inside a =IFERROR( so that the calculation will continue with the second list after all items from the first is
  2. Use the INDEX function to get values from the first list (i.e. INDEX($B$2:$B$5,ROWS(F$1:$F1)))
  3. Use another IFERROR( to return empty values instead of errors
  4. Use the INDEX function again to get values from the second list (i.e. INDEX($D$2:$D$7,ROWS(F$1:$F1)-ROWS($B$2:$B$5)))
  5. Finish the formula with second argument of IFERROR ""))
  6. Copy down the formula to the remaining rows as necessary

How

This formula combination will start with the first list to get values, and will return errors once there are no more items in the first list.

INDEX($B$2:$B$5,ROWS($F$1:F1))

If there is an error, the formula will move on to the values from the second list. We must also subtract the first list item count from our incremental index number.

INDEX($D$2:$D$7,ROWS($F$1:F1)-ROWS($B$2:$B$5))

If the second list comes to an end, the formula will return empty cells ("") to hide the errors.

Let's take a look at the ROWS functions here. The ROWS function returns the count of rows in a specified range. So, in a dynamic range like $F$1:F1, updating it to $F$1:F2 will return an incremental number with each row, because range in ROWS function expands.

For the second list, the INDEX function needs an index number starting from 1. To do this, auto-incremental ROWS formulas value should be subtracted from the first list item count. It is important that first list range should be absolute so that it won't return different values (i.e. ROWS($B$2:$B$5)).

Alternatively, the COLUMNS function can be used to merge two horizontal lists.

=IFERROR(

     INDEX($B$2:$B$5,ROWS($F$1:F1)),

     IFERROR(INDEX($D$2:$D$7,ROWS($F$1:F1)-ROWS($B$2:$B$5)),""))