# Array trouble

#### txfireguy015

##### New Member
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))}

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### jasonb75

##### Well-known Member
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).

Excel Formula:
``=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

##### Well-known Member
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

##### New Member
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

##### New Member

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

##### Well-known Member
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

##### New Member

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

##### New Member
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

##### Well-known Member
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

##### New Member
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!

Replies
1
Views
41
Replies
2
Views
77
Replies
8
Views
230
Replies
0
Views
84
Replies
13
Views
228

### Forum statistics

1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back