def _match(lookup_value, lookup_array, match_type=1): # Excel reference: https://support.office.com/en-us/article/ # MATCH-function-E8DFFD45-C762-47D6-BF89-533F4A37673A """ The relative position of a specified item in a range of cells. Match_type Behavior 1: return the largest value that is less than or equal to `lookup_value`. `lookup_array` must be in ascending order. 0: return the first value that is exactly equal to lookup_value. `lookup_array` can be in any order. -1: return the smallest value that is greater than or equal to `lookup_value`. `lookup_array` must be in descending order. If `match_type` is 0 and lookup_value is a text string, you can use the wildcard characters — the question mark (?) and asterisk (*). :param lookup_value: value to match (value or cell reference) :param lookup_array: range of cells being searched. :param match_type: The number -1, 0, or 1. :return: #N/A if not found, or relative position in `lookup_array` """ lookup_value = ExcelCmp(lookup_value) if match_type == 1: # Use a binary search to speed it up. Excel seems to do this as it # would explain the results seen when doing out of order searches. lookup_value = ExcelCmp(lookup_value) result = bisect_right(lookup_array, lookup_value) while result and lookup_value.cmp_type != ExcelCmp( lookup_array[result - 1]).cmp_type: result -= 1 if result == 0: result = NA_ERROR return result result = [NA_ERROR] if match_type == 0: def compare(idx, val): if val == lookup_value: result[0] = idx return True if lookup_value.cmp_type == 1: # string matches might be wildcards re_compare = build_wildcard_re(lookup_value.value) if re_compare is not None: def compare(idx, val): # noqa: F811 if re_compare(val.value): result[0] = idx return True else: def compare(idx, val): if val < lookup_value: return True result[0] = idx return val == lookup_value for i, value in enumerate(lookup_array, 1): if value not in ERROR_CODES: value = ExcelCmp(value) if value.cmp_type == lookup_value.cmp_type and compare(i, value): break return result[0]