Description: The MATCH() function in LibreOffice Calc does not work with entries that have tildes (~) (except if it is the last character), returning #N/A rather than the expected location. Steps to Reproduce: 1. Use the MATCH() function in Calc to search for match of a character with a tilde in the middle (not the last character in the cell, which works ok) 1a. Example: MATCH("Test~Middle",A1:A3,0) will never find a cell with the entry "Test~Middle" Actual Results: A Test!! =MATCH(A1,A1:A3,0) 1 TestEnd~ =MATCH(A2,A1:A3,0) 2 Test~Middle =MATCH(A3,A1:A3,0) #N/A =MATCH("Test~Middle",A1:A3,0) #N/A Expected Results: A Test!! =MATCH(A1,A1:A3,0) 1 TestEnd~ =MATCH(A2,A1:A3,0) 2 Test~Middle =MATCH(A3,A1:A3,0) 3 =MATCH("Test~Middle",A1:A3,0) 3 Reproducible: Always User Profile Reset: No Additional Info: The similar functions SUBSTITUTE() and FIND() both work as expected.
You most likely have Wildcards enabled (which is the default as in Excel), in which the tilde character is a meta character that "Escapes the special meaning of a question mark, asterisk, or tilde character that follows immediately after the tilde character." See https://help.libreoffice.org/7.3/en-GB/text/scalc/guide/wildcards.html With Wildcards enabled you have to double the tilde character to search for a literal tilde character, like "Test~~Middle". Or switch off using wildcards and regular expressions in formulas, under Tools - Options - LibreOffice Calc - Calculate.
I confirm that toggling wildcards does change the result. I also confirm that this behavior matches Microsoft Excel 2016. Finally, I note that "Test~~Middle" finds the cell of interest. However, I still feel that this is unexpected behavior, because the search criterion and the lookup array do not have the same behavior as each other. The lookup array is based on the exact value of the cell, while the search criterion has the wildcard processed. MATCH(SUBSTITUTE(A3,"~","~~"),A1:A3,0) finds the value. Also unexpected is that the other wildcards (Test?Middle and Test*Middle) both give behavior that I would expect - it is just the tilde acting as an escape character, but only for the search criterion, which gives unexpected results.