Array trouble

[txfireguy015]

I'm using INDEX/MATCH with 2 criteria to locate and transfer data from one spreadsheet to another in EXCEL 2019.
On sheet 1, column A is "die", column B is "copy" and column E is "weight per foot". In column E I am entering the formula below to search another spreadsheet columns D and E for the last entry of "die" and "copy" to always return the most recent "weight per foot" in column P. The sheet that I'm searching has data added daily so I need my ranges within the array to expand as many rows as possible, preferably the entire sheet as you'll notice in my formula. However, when I do this the result is always 0. If I limit my ranges to only the rows that contain data the it will return the proper value. If I increase my range by 1 row it goes back to 0. Currently I have 1,863 rows of data. Are my ranges too large? Am I limited to rows that contain actual data and no blank cells? Do I need to use a completely different formula?

{=INDEX('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$P$2:$P$1048576,MATCH(1,(A1495='[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$D$2:$D$1048576)*(B1495='[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$E$2:$E$1048576),1))}

 

[jasonb75]

Are the 'text' numbers in the single criteria cell or in the main table range?

Main table

=INDEX('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$P:$P,AGGREGATE(14,6,ROW('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$D$2:$D$10000)/(A1495='[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$D$2:$D$10000)/((B1495&"")='[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$E$2:$E$10000),1))

Criteria

=INDEX('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$P:$P,AGGREGATE(14,6,ROW('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$D$2:$D$10000)/(A1495='[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$D$2:$D$10000)/((B1495+0)='[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$E$2:$E$10000),1))

Note that the second one will fail if the criteria is not a number.