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.

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