Shorten Formula

[Guest]

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")

 

[Bob Phillips]

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)

 

[Guest]

I appreciate the suggestions, Much thanks

Perhaps put

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

in another cell, e.g. I3 and then use

=IF(COUNTIF('Movie List'!B:B,I3),"I have "&I3&" Remove from
QUE",IF(COUNTIF('Janets Movies'!A:A,I3),"Janet has "&I3&" Remove from
QUE",""))