Array Formula #N/A Error | Manually Typing Over Data Gives Accurate Result

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

[TABLE="width: 259"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Input Code[/TD]
[TD]Input Amount[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TT[/TD]
[TD]$1,300[/TD]
[TD]2.36[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]22[/TD]
[TD]$975[/TD]
[TD]2.65[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]22[/TD]
[TD]$400[/TD]
[TD]2.25[/TD]
[/TR]
</tbody>[/TABLE]

{=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.


[TABLE="width: 408"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Code[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Target Result[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD]$0[/TD]
[TD]$500[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]11[/TD]
[TD]$500[/TD]
[TD]$733[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]11[/TD]
[TD]$733[/TD]
[TD]$967[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]11[/TD]
[TD]$967[/TD]
[TD]$1,200[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]11[/TD]
[TD]$1,200[/TD]
[TD]$1,433[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]11[/TD]
[TD]$1,433[/TD]
[TD]$3,000[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]TT[/TD]
[TD]$0[/TD]
[TD]$270[/TD]
[TD]2.20[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]TT[/TD]
[TD]$270[/TD]
[TD]$405[/TD]
[TD]2.20[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]TT[/TD]
[TD]$405[/TD]
[TD]$670[/TD]
[TD]2.24[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]TT[/TD]
[TD]$670[/TD]
[TD]$822[/TD]
[TD]2.28[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]TT[/TD]
[TD]$822[/TD]
[TD]$1,164[/TD]
[TD]2.32[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]TT[/TD]
[TD]$1,164[/TD]
[TD]$1,522[/TD]
[TD]2.36[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]TT[/TD]
[TD]$1,522[/TD]
[TD]$3,000[/TD]
[TD]2.40[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]22[/TD]
[TD]$0[/TD]
[TD]$573[/TD]
[TD]2.25[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]22[/TD]
[TD]$573[/TD]
[TD]$600[/TD]
[TD]2.25[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]22[/TD]
[TD]$600[/TD]
[TD]$814[/TD]
[TD]2.35[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]22[/TD]
[TD]$814[/TD]
[TD]$850[/TD]
[TD]2.35[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]22[/TD]
[TD]$850[/TD]
[TD]$961[/TD]
[TD]2.65[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]22[/TD]
[TD]$961[/TD]
[TD]$999[/TD]
[TD]2.65[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]22[/TD]
[TD]$999[/TD]
[TD]$1,200[/TD]
[TD]2.75[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]22[/TD]
[TD]$1,200[/TD]
[TD]$1,300[/TD]
[TD]2.85[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]22[/TD]
[TD]$1,300[/TD]
[TD]$1,500[/TD]
[TD]2.85[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]22[/TD]
[TD]$1,500[/TD]
[TD]$1,900[/TD]
[TD]2.85[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]22[/TD]
[TD]$1,900[/TD]
[TD]$3,000[/TD]
[TD]2.85[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Try:

{=IFERROR(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),"")}
 
Upvote 0
DonnyF - thanks for the response but that just clears the cells of the #n/a. I'm getting the #n/a where I should be getting a valid result. Once I type over the Input Code, the formulas change from #n/a to the correct result...I just can't go throw 40K rows of data and manually type over every code to get the right results :(
 
Upvote 0
If manually re-typing the values in column F makes the fomula work,that indicates the values that were orignally in column F are not 'Exactly' what you think they are.

How did the values originally get into column F?
Are they the result of a formula?
Were they copy/pasted from some other source?

Perhaps they have leading and/or trailing spaces, like
" 11" or "11 " instead of just "11"
 
Upvote 0
I used an index match to pull them in from the array, then pasted them in as values to reduce the calculation burden on the worksheet. They are formatted as "General" which is the same way I formatted the codes in Column A of my array. Those were hand-typed and auto-filled.
 
Upvote 0
OK, assuming F3 is now a cell that would make the formula work if you manually retyped it's value.
Also assuming F3 is TT as shown in the sample data you posted.

BEFORE you retype the value, what do these formulas return

=LEN(F3)
=F3="TT"
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

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
Back
Top