Is a cell value in column B in column A? (LibreOffice-Calc)

Question

• Column A has a sorted-descending list of some bum's Top-250 movies, in the following format: Apocalypse Now (1979)
• Column B has a sorted list of My Top-100, in the same format.
• Both lists have been copied and pasted into a Notepad text doc to confirm they are similar simple ASCI text – no extra spaces at the end – etc. - and then pasted back into LibreofficeCalc.

I need a function for Column C that shows any of MY movies (B) that he has NOT listed in (A).

Psudo code:

• C1 = The cell value in B1 – is it anywhere in A1:A8000? If not – put B1 value into C1, otherwise leave blank.
• C2 = The cell value in B2 – is it anywhere in A1:A8000? If not – put B2 value into C2, otherwise leave blank.
• Etc.

I have searched and found these functions – none of which work, for whatever reason. I've modified them to 8000 as the upper range which I don't think I'll ever approach.

=IF(ISERROR(MATCH(B1,$A$1:$A$8000,0))=1,B1,"")
=IFERROR(MATCH(B1;$A$1:$A$8000;0);"")
=IFNA(VLOOKUP($B1;$A$1:$A$8000;1;0);"")
=IF(ISNA(VLOOKUP($B1;$A$1:$A$8000;1;0));"";VLOOKUP($B1;$A$1:$A$8000;1;0))
=IF(ISNA(VLOOKUP($B1,$A$1:$A$8000,1,0)),"",VLOOKUP($B1,$A$1:$A$8000,1,0))
=VLOOKUP(B1,$A$1:$A$8000,1,) 
=MATCH($B1;$A$1:$A$999;0) 

I'd prefer it to be a single cell function, and not VBA.

I actually solved this back in like 2001 using Excel. The trick then was I had to edit the cell and use Ctrl-Shift-Enter to create a “dynamic array”, so the function was bracketed in {} curly brackets. But now I'm using the latest LibreOffice Calc and can't get the @#$# syntax correct.

Thank you!!

Edit NOTE: testing with "A" and "00001" numbers produces very different results. Values have to look like this in both columns:

• Alice (1988)
• Barfly (1987)
• Clueless (1995)
• etc.

Answer 1

OK I've tested these in Open Office with the following results:-

=IF(ISERROR(MATCH(B1,$A$1:$A$8000,0))=1,B1,"")

Gives Error 508 because the commas need changing to semicolons.

**=IF(ISERROR(MATCH(B1;$A$1:$A$8000;0))=1;B1;"")**

is fine.

=IFERROR(MATCH(B1;$A$1:$A$8000;0);"")

Gives #Name? because IFERROR isn't recognised.

=IFNA(VLOOKUP($B1;$A$1:$A$8000;1;0);"")

Gives #Name? because IFNA isn't recognised.

=IF(ISNA(VLOOKUP($B1;$A$1:$A$8000;1;0));"";VLOOKUP($B1;$A$1:$A$8000;1;0))

Works but gives the opposite result.

**=IF(ISNA(VLOOKUP($B1;$A$1:$A$8000;1;0));B1;"")**

would be fine.

=IF(ISNA(VLOOKUP($B1,$A$1:$A$8000,1,0)),"",VLOOKUP($B1,$A$1:$A$8000,1,0))

Commas

=VLOOKUP(B1,$A$1:$A$8000,1,) 

Commas

=MATCH($B1;$A$1:$A$999;0) 

Works but just gives the position of the match.

Probably the easiest way of doing it is:-

**=IF(COUNTIF(A$1:A$8000;B1);"";B1)**

Unfortunately it does seem that strings with brackets in are giving spurious matches in Libre/Open Office. You could get round it by a substitution I guess

=IF(COUNTIF(SUBSTITUTE(SUBSTITUTE(A$1:A$10;"(";"<");")";">");SUBSTITUTE(SUBSTITUTE(B1;"(";"<");")";">"));"";B1)

entered as an array formula and copied (rather than pulled) down or of course global edit all the brackets :-(.

Now that I know the root cause of this thanks to @Lyrl, there is a further option of turning off the regular expressions as suggested or you could escape the brackets:-

=IF(COUNTIF(A$2:A$11;SUBSTITUTE(SUBSTITUTE(B2;"(";"\(");")";"\)"));"";B2)

See documentation on Regex in Open Office here