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
|