cooker6583
New Member
- Joined
- Aug 17, 2016
- Messages
- 6
Hello - I am working with a 40,000+ row excel file and using an index/match array formula to assign values. The lookup ranges are fixed ranges and I'm finding that when I fill down, I'm getting #N/A errors. My formulas work with Ctrl+D fill down on a small scale/test data, but when I apply it to the large data set with Ctrl+D I get the errors.
The interesting thing is that when I manually type input code on the main file (i.e., type over the data in Column F below with the exact same entry), the formulas work.
It's like Excel isn't reading the data for some reason. In other words, I believe my formulas are accurate and this has something to do with how the array fills. I've tried a few of the basic tricks (highlight full range, then type formula, then CSE, etc) but can't break through.
Here is an example of my data and array formulas (sheet 2 is just the name of my worksheet):
<tbody>
</tbody>
{=INDEX(Sheet2!$D$3:$D$44,MATCH(F3,IF(G3>=Sheet2!$B$3:$B$44,IF(G3<=Sheet2!$C$3:$C$44,Sheet2!$A$3:$A$44)),0),1)}
I've also tried eliminating the conditional if statements using the following:
{=INDEX(Sheet2!$D$3:$D$44,MATCH(1,(F3=Sheet2!$A$3:$A$44)*(G3>=Sheet2!$B$3:$B$44)*(G3<=Sheet2!$C$3:$C$44),0))}
The array I'm referencing is set up like below.
<tbody>
</tbody>
The interesting thing is that when I manually type input code on the main file (i.e., type over the data in Column F below with the exact same entry), the formulas work.
It's like Excel isn't reading the data for some reason. In other words, I believe my formulas are accurate and this has something to do with how the array fills. I've tried a few of the basic tricks (highlight full range, then type formula, then CSE, etc) but can't break through.
Here is an example of my data and array formulas (sheet 2 is just the name of my worksheet):
F | G | H | |
Input Code | Input Amount | Result | |
3 | TT | $1,300 | 2.36 |
4 | 22 | $975 | 2.65 |
5 | 22 | $400 | 2.25 |
<tbody>
</tbody>
{=INDEX(Sheet2!$D$3:$D$44,MATCH(F3,IF(G3>=Sheet2!$B$3:$B$44,IF(G3<=Sheet2!$C$3:$C$44,Sheet2!$A$3:$A$44)),0),1)}
I've also tried eliminating the conditional if statements using the following:
{=INDEX(Sheet2!$D$3:$D$44,MATCH(1,(F3=Sheet2!$A$3:$A$44)*(G3>=Sheet2!$B$3:$B$44)*(G3<=Sheet2!$C$3:$C$44),0))}
The array I'm referencing is set up like below.
A | B | C | D | |
2 | Code | Min | Max | Target Result |
3 | 11 | $0 | $500 | 3.00 |
4 | 11 | $500 | $733 | 3.00 |
5 | 11 | $733 | $967 | 3.00 |
6 | 11 | $967 | $1,200 | 3.00 |
7 | 11 | $1,200 | $1,433 | 3.00 |
8 | 11 | $1,433 | $3,000 | 3.00 |
9 | TT | $0 | $270 | 2.20 |
10 | TT | $270 | $405 | 2.20 |
11 | TT | $405 | $670 | 2.24 |
12 | TT | $670 | $822 | 2.28 |
13 | TT | $822 | $1,164 | 2.32 |
14 | TT | $1,164 | $1,522 | 2.36 |
15 | TT | $1,522 | $3,000 | 2.40 |
16 | 22 | $0 | $573 | 2.25 |
17 | 22 | $573 | $600 | 2.25 |
18 | 22 | $600 | $814 | 2.35 |
19 | 22 | $814 | $850 | 2.35 |
20 | 22 | $850 | $961 | 2.65 |
21 | 22 | $961 | $999 | 2.65 |
22 | 22 | $999 | $1,200 | 2.75 |
23 | 22 | $1,200 | $1,300 | 2.85 |
24 | 22 | $1,300 | $1,500 | 2.85 |
25 | 22 | $1,500 | $1,900 | 2.85 |
26 | 22 | $1,900 | $3,000 | 2.85 |
<tbody>
</tbody>