There are no repeats, it is against different sheets.

You could define a name of say look_val for

MID(H3,FIND("-",H3)+2,1000)

and use that in the formula

=IF(ISNA(VLOOKUP(lookup_val,'Movie List'!B:C,1,FALSE)),
IF(ISNA(VLOOKUP(lookup_val,'Janets Movies'!A:B,2,FALSE)),"","Janet has
"&lookup_val&" Remove from QUE"),
"I have "&lookup_val&" Remove from QUE")

but actually VLLOKUP is overkill, only needs MATCH

=IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Movie List'!B:B,0)),
IF(ISNA(MATCH(MID(H3,FIND("-",H3)+2,1000),'Janets
Movies'!A:A,0)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove from
QUE"),
"I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE")

or

=IF(ISNA(MATCH(lookup_val,'Movie List'!B:B,0)),
IF(ISNA(MATCH(lookup_val,'Janets Movies'!A:A,0)),"","Janet has
"&lookup_val&" Remove from QUE"),
"I have "&lookup_val&" Remove from QUE")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Lemmesee" wrote in message
...
I am sure that this formula can be shorter and take out the repeats.

=IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Movie
List'!B:C,1,FALSE)),IF(ISNA(VLOOKUP(MID(H3,FIND("-",H3)+2,1000),'Janets
Movies'!A:B,2,FALSE)),"","Janet has "&MID(H3,FIND("-",H3)+2,1000)&" Remove
from QUE"),"I have "&MID(H3,FIND("-",H3)+2,1000)&" Remove from QUE")