#N/A error 1

[eit09]

I am using the match function in cell k5 and k6. Every so often the cell k5 shows #N/A when I paste data into the highlighted cells. Can someone tell me what I can do to correct this error from happening? I have attached the spread sheet.

 

[eit09]

First upload doesn't work try this one.

 

[CajunCenturion]

I would try two things. First, I would change the starting cell of the match array from F1 to F2 since F1 is a header. Secondly, I would try using the numeric value of 0.00 instead of the text value "No shear".

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

And remove the zero as the match type.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[eit09]

I Tried all of the above but didn't do it. I need the zero in the match type as I am looking up the exact #. Any other suggestions?

 

[CajunCenturion]

Make sure that the precision of the lookup value matches the precision of the lookup table.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TDAA]

K5 is trying to match J5 to the array. With the data you have in there, 22.63 is the max shear you have. However, this value is based on an absolute value of your shear, and in the array the "max value" is negative.

Use:

=IF(ISERROR(MATCH(J5,F1:F1061,0)),MATCH(-J5,F1:F1061,0),MATCH(J5,F1:F1061,0))

 

[IDS]

Replace "no shear" in the formula in column F with 0, then put =ABS(...) around the whole formula.

You are using an absolute value as your lookup value, so the numbers in your lookup column must be absolute as well.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[sdz]

As TDAA and IDS say you are trying to match a negative number to a positive one.
You can also get problems if one is formatted as text and the other is a number.

 

[TDAA]

The text "No Shear" within the column has no effect. The numbers will not be considered text since they are a calculated value. The formula I put up will give the right row for a positive or negative result.

One thing to note, you have multiple values in the sheet that would be the maximum (at least as displayed for the 22.63), so you may have a different row with the same maximum.