MATCH(SearchCriterion; LookupArray; Type) SearchCriterion is the value which is to be searched for in the single-row or single-column array. LookupArray is the reference searched. A lookup array can be a single row or column, or part of a single row or column. Type may take the values 1, 0, or -1. If Type = 1 or if this optional parameter is omitted, it is assumed that the first column of the search array is sorted in ascending order. If Type = -1 it is assumed that the column is sorted in descending order. This corresponds to the same function in Microsoft Excel. If Type = 0, only exact matches are found. If the search criterion is found more than once, the function returns the index of the first matching value. Only if Type = 0 Enhancement requested: *** or Type > 1 *** can you search for regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options). If Type = 1 or the third parameter is omitted, the index of the last value that is smaller than or equal to the search criterion is returned. This applies even when the search array is not sorted. For Type = -1, the index of the first value that is larger than or equal is returned. Enhancement requested: *** If Type > 1, only exact matches are found. If the search criterion is found more than once, the function returns the index of the Type'th matching value. VLOOKUP(SearchCriterion; Array; Index; Sorted) HLOOKUP(SearchCriterion; Array; Index; Sorted) Enhancement requested: *** Sorted does the same as the Type parameter in function MATCH. *** This enhancement is compatible with existing MATCH, HLOOKUP and VLOOKUP.
Hi David, Please don't report multiple requests in a single report. Closing this as INVALID. Enhancement reports just like bug reports need to be 1 issue per bug report. As for the specific requests, they seem VERY unlikely to be implemented for various reasons: 1) This would cause some interop issues that we try to avoid when possible because people will then start yelling and screaming when they take a spreadsheet to work (or send it to a colleague) and see different results in Excel; 2)Can you provide an example of where this one would be useful? Is this how Excel handles it? 3)Again, example and is this how Excel does it. I know it's not ideal to be limited by Excel but it is the general standard. If we want to avoid hundreds of interop bug reports and angry users who see differences between the applications, we aim for interop over features that likely would only help 1 or 2 people. Please take these things into consideration as you decide whether to create new tickets. Thanks.
If I'm not wrong with Menu/Tools/Options/LibreOffice calc/Calculate - Search criteria = and <> must applied to the whole cells with Type = 0 works as you like it.