## merge two sheets with array formula

*Article updated on March 02, 2009*

**Question:** How do i automatically merge two lists on two different sheets but with identical headers?

**Answer:** I used this formula in all of the cells: =IF(IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1,ROW()-SUM( IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))=0,"", IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1, ROW()-SUM(IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))) + Ctrl + Shift + Enter

Here is the result:

So what is this formula really doing. Let us start with a smaller part of the formula:

OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1) retrieves the values on Sheet1.

IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1), OFFSET(Sheet3!$A$1,ROW()-SUM( IF(Sheet1!$A$1:$A$25<>"",1,0)) checks if the values on sheet1 are ending and if so starts to get the values on sheet3.

=IF(IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1,ROW()-SUM( IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))=0,"", IF(SUM(IF(Sheet1!$A$1:$A$25<>"",1,0))>=ROW(), OFFSET(Sheet1!$A$1,ROW()-1,COLUMN()-1),OFFSET(Sheet3!$A$1, ROW()-SUM(IF(Sheet1!$A$1:$A$25<>"",1,0)),COLUMN()-1))) checks if the values on sheet3 equals 0 and if so display nothing.

Download excel sample file for this tutorial.

merge-two-sheets.xls

(Excel 97-2003 Workbook *.xls)

**SUM(**number1,[number2],>**)
**Adds all the numbers in a range of cells

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

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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article