#N/A error

  • Thread starter Thread starter DavidObeid
  • Start date Start date
D

DavidObeid

I used the lookup wizard to create the following formula:

=INDEX($A$8:$AE$2509, MATCH(0,$A$8:$A$2509,), MATCH(1,$A$8:$AE$8,))

I modified the formula to look like this:

=G1/A7*INDEX($A$8:$AE$2509, MATCH($G$2,$A$8:$A$2509,),
MATCH($G$3,$A$8:$AE$8,))

So that a user can be prompted to enter the desired row (in cell G2)
and column (in cell G3) of the table that is being searched
($A$8:$AE$2509). The modified formula was placed in cell G4.

Now, when *some* values are entered in G2, everything works fine, but
when other values are entered, I get a #N/A error.

The values entered all come from column A (as values, not as
references).

The values in column A are the percentages 0.00% upto 25.00% with 0.01%
increments (generated by filling down from A10 down with =A9+$B$7 to
A2509, where B7 contains 0.01%) .

0.00%, 0.01%, 0.02% all work fine.

0.03% gives the error

0.04% is fine

0.05% is fine

0.06 and 0.07 give the error

I haven't test much more than these.

What is going wrong?
 
DavidObeid said:
I used the lookup wizard to create the following formula:

=INDEX($A$8:$AE$2509, MATCH(0,$A$8:$A$2509,), MATCH(1,$A$8:$AE$8,))

I modified the formula to look like this:

=G1/A7*INDEX($A$8:$AE$2509, MATCH($G$2,$A$8:$A$2509,),
MATCH($G$3,$A$8:$AE$8,))

So that a user can be prompted to enter the desired row (in cell G2)
and column (in cell G3) of the table that is being searched
($A$8:$AE$2509). The modified formula was placed in cell G4.

Now, when *some* values are entered in G2, everything works fine, but
when other values are entered, I get a #N/A error.

The values entered all come from column A (as values, not as
references).

The values in column A are the percentages 0.00% upto 25.00% with 0.01%
increments (generated by filling down from A10 down with =A9+$B$7 to
A2509, where B7 contains 0.01%) .

0.00%, 0.01%, 0.02% all work fine.

0.03% gives the error

0.04% is fine

0.05% is fine

0.06 and 0.07 give the error

I haven't test much more than these.

What is going wrong?
Click to expand...

One possibility:
You are using MATCH functions without a third argument, i.e. you are looking
for an 'approximate' match. In this case the data in the lookup ranges
($A$8:$A$2509 and $A$8:$AE$8) must be in ascending order. Is this true of
your data?
 
Thanks.

I experimented with different match types and the problem has appeare
to resolve itself with a match type of 1.

Regards,

David Obei
 
Back
Top