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]

When you multiply in a match array, all results are valid regardless of whether or not they meet the criteria.

Here's an alternative that is set up for 10k rows of data, full columns is not recommended with any kind of array formula as it will be slow to process.
In addition to this, any formula where the range specifically refers to row 1048576 will fail if you insert rows into the sheet, to be more specific the last rows will fall off of the end of the sheet and generate #REF errors (noting that your formulas refer to another workbook, behaviour may be different. I would have to set up test files to be certain of the exact effect).

=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))

 

[steve the fish]

You want the last 'hit'? You could try like this:

=LOOKUP(2,1/(('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$D$1:$D$10000=A1495)*('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$E$1:$E$10000=B1495)),'[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$P$1:$P$10000)

 

[txfireguy015]

I'm not able to get either one of these to work for some reason. I even decreased my ranges to about 20 rows of data. The first one returns "#NUM!" and the second one returns "#N/A". My data is not sorted. Could this be the problem?

 

[txfireguy015]

You want the last 'hit'? You could try like this:

=LOOKUP(2,1/(('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$D$1:$D$10000=A1495)*('[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$E$1:$E$10000=B1495)),'[Levi''s Efficiency Press Report 2020.xlsx]Extrude Production'!$P$1:$P$10000)

You are correct, I need to find the last 'hit'.

 

[jasonb75]

The first one returns "#NUM!" and the second one returns "#N/A". My data is not sorted. Could this be the problem?

The order would make no difference, the errors that you are seeing tell me that there is no match for the specified criteria.

 

[txfireguy015]

The order would make no difference, the errors that you are seeing tell me that there is no match for the specified criteria.

I did some further research and used the 'evaluate formula' function. It is finding the match for the first criteria, but when it goes to the second it saying none of them match. However, they all actually match. I narrowed it down to 11 lines of data so I can look at both sheets on one screen at the same time. I've even tried formatting that particular column of data on each sheet as number and as text and it makes no difference. The data I'm looking for in this particular instance is a '1' and all entries for that column in my second sheet are '1'.

 

[txfireguy015]

I did some further research and used the 'evaluate formula' function. It is finding the match for the first criteria, but when it goes to the second it saying none of them match. However, they all actually match. I narrowed it down to 11 lines of data so I can look at both sheets on one screen at the same time. I've even tried formatting that particular column of data on each sheet as number and as text and it makes no difference. The data I'm looking for in this particular instance is a '1' and all entries for that column in my second sheet are '1'.

I changed the '1' on my first sheet to an 'a' and then changed the '1' on my second sheet to an 'a' only on the line I want it to match up with. This returned the proper value. Once I changed it back to '1' on both pages it worked. I then made another line within the range match both criteria. It now wants to return the first match where I need it to return the last match.

 

[jasonb75]

The formula in post 2 is for the last match, you would use 15 as the first part of aggregate for the first match, 14 is for last.

I've even tried formatting that particular column of data on each sheet as number and as text and it makes no difference.

Changing format between text and number makes no difference unless you re-enter the data. If one sheet was originally text and the other was number then they wouldn't match.

 

[txfireguy015]

The formula in post 2 is for the last match, you would use 15 as the first part of aggregate for the first match, 14 is for last.


Changing format between text and number makes no difference unless you re-enter the data. If one sheet was originally text and the other was number then they wouldn't match.

Ok, it looks like that formula is working as long as I re-enter the data like you said. Thanks for the help!