Can't figure out the best way to combine data from separate worksheets. Index? Match?

In the attached file, I'm trying to combine rows from tab 1 and tab 2.

In the combined tab, it should pull data from tab 1 (including blank cells), until an entire row is blank, and then go onto tab 2 and pull the same.

In the combined tab, there are no blank rows (between tab 1 & 2 data) until all the data has been pulled.

Unless I missed something, a simple copy and paste from both sheets (Tab1 then Tab2) would achieve the end result (based on your sample file),

Unfortunately, it's more complicated than the example I posted. I am receiving data from about a dozen different sources and the data isn't consistent across all customers. So if I copy paste, I would have to copy column by column (often various columns) depending on which customer.

So that's why I would like to do a lookup on column heading.

Can you post a more "complicated" file as it still isn't obvious to me (unless your columns are inconsistent) why you cannot copy rows and pate.

Try this for column A to combine the two tabs: If it works change INDEX ranges to other columns.

=IF('Tab 2'!$A2="",IFERROR(INDEX('Tab 1'!A:A,SMALL(INDEX(('Tab 1'!$A$2:$A$100="")*10^10+ROW('Tab 1'!$A$2:$A$100),0),ROWS($1:1)-COUNTIF('Tab 2'!$A$2:$A$100,"<>"))),""),IFERROR(INDEX('Tab 2'!A:A,SMALL(INDEX(('Tab 2'!$A$2:$A$100="")*10^10+ROW('Tab 2'!$A$2:$A$100),0),ROWS('Tab 2'!$A$2:$A2))),""))

ASSUMPTION: There are no blanks between rows in column A .