{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC! $Q$30:$T$30,1)),0)INDEX(CC!$Q$31:$T$990,MATCH(OrdA ck!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

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 wrote:

{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!M667,CC!$C$31:$C$990,0),MATCH(OrdAck!J667,CC! $Q$30:$T$30,1)),0)INDEX(CC!$Q$31:$T$990,MATCH(OrdA ck!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

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" wrote in message
...
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 wrote:


{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
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 wrote:

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" wrote in message
...
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 wrote:


{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

--

Dave Peterson

--

Dave Peterson

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$9 90,0)*MATCH(OrdAck!J663,CC
!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:



{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

--

Dave Peterson

--

Dave Peterson

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

=IF(ISNUMBER(J663*MATCH(OrdAck!M663,cc!$C$31:$C$99 0,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)

Pat wrote:

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$9 90,0)*MATCH(OrdAck!J663,CC
!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:



{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

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" wrote in message
...
Your original formula had that ,0) at the end.

=IF(ISNUMBER(J663*MATCH(OrdAck!M663,cc!$C$31:$C$99 0,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)

Pat wrote:

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$9 90,0)*MATCH(OrdAck!J663,CC

!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:




{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

--

Dave Peterson

--

Dave Peterson

--

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?



Pat wrote:

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" wrote in message
...
Your original formula had that ,0) at the end.

=IF(ISNUMBER(J663*MATCH(OrdAck!M663,cc!$C$31:$C$99 0,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)

Pat wrote:

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$9 90,0)*MATCH(OrdAck!J663,CC

!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:




{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Not sure what I need to clarify!

"Dave Peterson" wrote in message
...
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?



Pat wrote:

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" wrote in message
...
Your original formula had that ,0) at the end.

=IF(ISNUMBER(J663*MATCH(OrdAck!M663,cc!$C$31:$C$99 0,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)

Pat wrote:

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$9 90,0)*MATCH(OrdAck!J663,CC


!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:





{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

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,cellwi thsecondformula)



Pat wrote:

Not sure what I need to clarify!

"Dave Peterson" wrote in message
...
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?



Pat wrote:

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" wrote in message
...
Your original formula had that ,0) at the end.

=IF(ISNUMBER(J663*MATCH(OrdAck!M663,cc!$C$31:$C$99 0,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)

Pat wrote:

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$9 90,0)*MATCH(OrdAck!J663,CC


!$Q$30:$T$30,1)),INDEX(CC!$Q$31:$T$990,MATCH(OrdAc k!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" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:





{=IF(ISNUMBER(J667),INDEX(CC!$Q$31:$T$990,MATCH(Or dAck!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(Or dAck!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$3 0:$T$30,1)),0)}

this is obviously not correct, can someone help!

Thanks
Pat

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson