txfireguy015
New Member
- Joined
- Jan 28, 2021
- Messages
- 6
- Office Version
- 2019
- Platform
- Windows
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))}
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))}