Indexing formula with multiple sheets

So I'm having an issue pulling data from two sheets that have the same headings into a master sheet where all the data should be kept. The "Initial Invoices" sheet and "Secondary Invoices" are the sheets where the data should be pulled from. I understand how to use an index array formula to pull data from another sheet in excel but I'm having trouble when I need to pull the data from two sheets into one giant sheet which will ultimately contain every invoice.

Here's the formula I'm attempting and having trouble with...It's clearly wrong as you'll see the results are adding whats being reported for "Table2" to whats being reported in "table3" but its how I imagined the formula should work. Hopefully you'll understand what I'm referring to as you see the sample file.

=IFERROR(INDEX(Table2+Table3[#Data],SMALL(IF((Table2[PO '#]<>"")+(Table3[PO '#]<>""),ROW(Table2[PO '#])-MIN(ROW(Table2[PO '#]))+1,""),ROWS($1:2)), MATCH(A$1,'Initial Invoices'!$A$1:$D$1,0)),"")

So as youll see in the "master invoices" sheet, the formula is only reporting 9 rows of data when it should be reporting all 14 distinct PO #'s. (9 from Table2 and 5 from Table3) Instead of adding the two numbers together.

Thank you in advance for your help
Mike Stafford

Hello
Take a look at the attached reply. Does this do as required?

DBY

Yes that's what I'm looking for, I should have included another sheet because my actual excel file is referencing three sheets instead of two like the sample shows. Is there a formula that will pull data from three sheets? I tried inputting another Index command after the second in the formula you wrote but it returns nothing at all when I do so.

This is how mine looks:
=IFERROR(IF(ROWS(B$2:B2)<=COUNTA(Table2[PO Date]),INDEX(Table2[PO '#],ROWS(B$2:B2)),INDEX(Table3[PO '#],ROWS(B$2:B2),INDEX(Table4[PO '#],ROWS(B$2:B2)-COUNTA(Table2[PO Date]))),"")

Thankyou

Hello
I've amended the formula for three tables. I've used an helper table and column to make things easier but you could incorporate these calculations into the formula. It seems to work OK but you'll perhaps need to test this out on some real data, I suggest a copy not your actual file.

DBY

Happy to have helped. It was a bit of a logic puzzle.