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

[cooker6583]

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]

 

[DonnyF]

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),"")}

 

[cooker6583]

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

 

[Jonmo1]

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"

 

[cooker6583]

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.

 

[Jonmo1]

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"

 

[cooker6583]

=Len(f3) returns 2
=F3="TT" returns true

 

[cooker6583]

After I manually type over the =F3="TT" turned from True to False.