Check for errors

[Pat]

{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(OrdAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)}

The above formula does not check for errors which results in #N/A being
returned on some cells where the formula is used. I have changed it to:

{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(OrdAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)INDEX(CC!$Q$31:$T$990,MATCH(OrdAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

 

[Dave Peterson]

I think that the portion of the formula that could break is when you look for
the matches for the column/row hed

so maybe you could just test to make sure that the matches came back with
numbers:

=if(and(isnumber(j667),
isnumber(MATCH(OrdAck!M667,CC!$C$31:$C$990,0)
*MATCH(OrdAck!J667,CC!$Q$30:$T$30,1))), index(.....)))

Since you're using isnumber() in the first test, you could include that in a
single test:

=if(isnumber(j667 * MATCH(OrdAck!M667,CC!$C$31:$C$990,0)
* MATCH(OrdAck!J667,CC!$Q$30:$T$30,1)), index(.....)))

(Watch out for missing/extra parentheses!!)

 

[Pat]

Dave,
This is a belated response, I did not come across your message until I reset
this newsgroup.
Both your suggestions returned a result if J667 contained a value. However
both formulas returned FALSE if J667 contained no value. It also made no
difference if J667 is formatted as a number.

I will repost a new message in the unlikely event you or others in the
newsgroup do not see this message.

Cheers
Pat

 

[Dave Peterson]

Did you include the 0 at the end of your formula?

(the False portion of the if/then/else structure).

(I'll look for the other post, too.)

 

[Pat]

Did you include the 0 at the end of your formula?
Here is the two formulas as you suggested:

{=IF(AND(ISNUMBER(J664),ISNUMBER(MATCH(OrdAck!M664,CC!$C$31:$C$990,0)*MATCH(
OrdAck!J664,CC!$Q$30:$T$30,1))),INDEX(CC!$Q$31:$T$990,MATCH(OrdAck!M664,CC!$
C$31:$C$990,0),MATCH(OrdAck!J664,CC!$Q$30:$T$30,0)))}


{=IF(ISNUMBER(J663*MATCH(OrdAck!M663,CC!$C$31:$C$990,0)*MATCH(OrdAck!J663,CC
!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAck!M663,CC!$C$31:$C$990,0),
MATCH(OrdAck!J663,CC!$Q$30:$T$30,0)))}

I hope I have interpeted them correctly.

(I'll look for the other post, too.)

I had been called away before I could send another post now you have seen
this post there is now no need to repost

Cheers
Pat

 

[Dave Peterson]

Your original formula had that ,0) at the end.

=IF(ISNUMBER(J663*MATCH(OrdAck!M663,cc!$C$31:$C$990,0)
*MATCH(OrdAck!J663,cc!$Q$30:$T$30,1)),
INDEX(cc!$Q$31:$T$990,MATCH(OrdAck!M663,cc!$C$31:$C$990,0),
MATCH(OrdAck!J663,cc!$Q$30:$T$30,0)),
0)
(still array entered)

Did you include the 0 at the end of your formula?

Here is the two formulas as you suggested:

{=IF(AND(ISNUMBER(J664),ISNUMBER(MATCH(OrdAck!M664,CC!$C$31:$C$990,0)*MATCH(
OrdAck!J664,CC!$Q$30:$T$30,1))),INDEX(CC!$Q$31:$T$990,MATCH(OrdAck!M664,CC!$
C$31:$C$990,0),MATCH(OrdAck!J664,CC!$Q$30:$T$30,0)))}

{=IF(ISNUMBER(J663*MATCH(OrdAck!M663,CC!$C$31:$C$990,0)*MATCH(OrdAck!J663,CC
!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAck!M663,CC!$C$31:$C$990,0),
MATCH(OrdAck!J663,CC!$Q$30:$T$30,0)))}

I hope I have interpeted them correctly.

(I'll look for the other post, too.)

I had been called away before I could send another post now you have seen
this post there is now no need to repost

Cheers
Pat

 

[Pat]

That returned an interesting result!
By adding ,0) at the end certainly cleared any errors, but if J663 contains
a value it now returns an #N/A error.
A reversal of fortunes you could say!

 

[Dave Peterson]

I didn't create any test data, but if you break your formula into smaller
pieces:

=MATCH(OrdAck!M663,cc!$C$31:$C$990,0)
=MATCH(OrdAck!J663,cc!$Q$30:$T$30,0)

Ooooh. I think I see the problem. =match() wants a single column or single
row.

That second =match() has Q30:T30. Which should change?

That returned an interesting result!
By adding ,0) at the end certainly cleared any errors, but if J663 contains
a value it now returns an #N/A error.
A reversal of fortunes you could say!

 

[Pat]

Not sure what I need to clarify!

I didn't create any test data, but if you break your formula into smaller
pieces:

=MATCH(OrdAck!M663,cc!$C$31:$C$990,0)
=MATCH(OrdAck!J663,cc!$Q$30:$T$30,0)

Ooooh. I think I see the problem. =match() wants a single column or single
row.

That second =match() has Q30:T30. Which should change?

 

[Dave Peterson]

Ooh. Never mind. I misread it again!

(I saw Q30:T990. (Time to visit Lenscrafters again!)).

But still put those little pieces of formulas in separate cells and see what
they evaluate to.

Then build a formula like:

=INDEX(cc!$Q$31:$T$990,cellwithfirstformula,cellwithsecondformula)

Not sure what I need to clarify!