return opposite

[Pat]

{=IF(ISNA(MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0)),"",IF(INDEX(PF05!BD$24:BD$
1000,MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0))="",INDEX(PF05!$BE$24:$BE$1000,M
ATCH(CC!$C102,PF05!$BM$24:$BM$1000,0)),""))}

The above formula is doing the opposite of what I want it to do, a value
should only be returned if there is a corresponding value in the cell of BD

Thank you if you can help.
Pat

[duane]

Try This

{=if(isna(match(cc!$c102,pf05!$bm$24:$bm$1000,0)), "",if(index(pf05!bd$24:bd$1000,match(cc!$c102,pf05!$bm$24:$bm$1000,0))="","",
Index(pf05!$be$24:$be$1000,match(cc!$c102,pf05!$bm$24:$bm$1000,0))))}

[Max]

Try instead, array-entered*:

=IF(ISNA(MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0)),"",IF(INDEX(PF05!BD$24:BD$1
000,MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0))="","",INDEX(PF05!$BE$24:$BE$1000
,MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0))))

*press CTRL+SHIFT+ENTER

Amended the VALUE_IF_TRUE return of this part:

... IF(INDEX(PF05!BD$24:BD$1000,MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0))="",
....

to be a blank ("") instead

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Pat" <[email protected]> wrote in message
news:[email protected]...
>
{=IF(ISNA(MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0)),"",IF(INDEX(PF05!BD$24:BD$
>
1000,MATCH(CC!$C102,PF05!$BM$24:$BM$1000,0))="",INDEX(PF05!$BE$24:$BE$1000,M
> ATCH(CC!$C102,PF05!$BM$24:$BM$1000,0)),""))}
>
> The above formula is doing the opposite of what I want it to do, a value
> should only be returned if there is a corresponding value in the cell of
BD
>
> Thank you if you can help.
> Pat
>
>

[Max]

Sorry, think the originally posted and the modified expression
need *not* be array-entered.

Just "normal" enter (i.e. press ENTER) will do
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----