Array trouble

txfireguy015

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

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,434
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 28, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jan 28, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,434
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 28, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 28, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,434
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 28, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top