Match returning #N/A

[Frank Pytel]

The match function is not working for me. Please help.

I have a calculated cell that returns a number. I have verified that it is
in fact a number using two methods.

=isnumber(p2) returns TRUE

=if(isnumber(p2),"Red","Dork") returns Red.

The number is calculated with the following formula

=(INDIRECT("'import'!"&"o"&MID(C2,2,10))/30.48)/10

The above returns 24.25 as a number.

My perfect goal would be an Index() function, but the index must be returned
from 2 values. I am working in 3 dimensional space and need to return the
value from the End points of the X and the Y axis. I thought that a better
approach might be to Match() the Row of the X and the Column of the Y inside
of the Index() but this is not working so I am going back to Address().

The formula that I am trying to start with follows:

=MATCH(P2,'Design Criteria'!D1427,0)

That is the formula that is returning #N/A. The error is being returned no
matter if my Match type is 1, 0 or -1. When I exchange 24.25 for the P2
reference, it works fine.

Can anyone help me with this please?? Thank you very, very much.

God Bless

Frank Pytel

 

[Pete_UK]

You probably have a rounding error, so that what you see as 24.25
might actually be 24.250000001, or 24.249999997. What you need to do
is to modify the formula that produces this result as follows:

=ROUND((INDIRECT("'import'!"&"o"&MID(C2,2,10))/30.48)/10,2)

and this will round the value to 2 decimal places.

Hope this helps.

Pete

 

[Glenn]

The match function is not working for me. Please help.

I have a calculated cell that returns a number. I have verified that it is
in fact a number using two methods.

=isnumber(p2) returns TRUE

=if(isnumber(p2),"Red","Dork") returns Red.

The number is calculated with the following formula

=(INDIRECT("'import'!"&"o"&MID(C2,2,10))/30.48)/10

The above returns 24.25 as a number.

My perfect goal would be an Index() function, but the index must be returned
from 2 values. I am working in 3 dimensional space and need to return the
value from the End points of the X and the Y axis. I thought that a better
approach might be to Match() the Row of the X and the Column of the Y inside
of the Index() but this is not working so I am going back to Address().

The formula that I am trying to start with follows:

=MATCH(P2,'Design Criteria'!D1427,0)

That is the formula that is returning #N/A. The error is being returned no
matter if my Match type is 1, 0 or -1. When I exchange 24.25 for the P2
reference, it works fine.

Can anyone help me with this please?? Thank you very, very much.

God Bless

Frank Pytel

Does the formula in P2 result in EXACTLY 24.25, or are you only displaying two
decimal places?

Are the values in D1427 sorted?

 

[vezerid]

Some thoughts...

Is it certain that the INDIRECT formula returns 24.25 and not e.g.
24.2498129405? But then again one of 1, -1 as MATCH 3rd argument
should not return #N/A.

Try the inline evaluation feature for the MATCH formula. In the
formula bar first select the P2 reference and press F9. Then do the
same for 'Design Criteria'!D1427. See what values the formula bar
shows. Are they numbers? Any chance these values are text?

HTH
Kostis Vezerides

 

[Frank Pytel]

Pete;

Brilliant. I am not sure what the number was, but I took it out to 30
decimal places and it wasn't getting caught. I tried that once before and
must have miss keyed.

Thanks Pete

Frank Pytel

 

[Frank Pytel]

Glenn;

Pete got it. The round() worked great

Thanks for your reply anyway.

Frank

 

[Frank Pytel]

vezerid;

Pete got it. The round() worked great

Thanks for your reply anyway. I hadn't known about the F9. I will have to
check into more of those keys.

Have a Great Day!

Frank

 

[Pete_UK]

You're welcome, Frank - glad to be of help.

Pete