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

Related posts:

1. Merge two lists
2. Merge two columns into one list in excel
3. Merge three columns into one list in excel
4. Search three columns on three sheets, part 2
5. Extract numbers and text from a range using array formula in excel
6. Identify largest text value in a column using array formula in excel
7. Search three columns on three sheets
8. Sort text values by length using array formula in excel
9. Filter unique rows and sort by date using array formula in excel
10. Find the largest number in a dynamic column using excel formula