def test_match_crazy_order(lookup_array, lookup_value, result1, result0,
resultm1):
assert result0 == match(lookup_value, lookup_array, 0)
assert resultm1 == match(lookup_value, lookup_array, -1)
if result1 != match(lookup_value, lookup_array, 1):
lookup_array = [ExcelCmp(x) for x in lookup_array]
if sorted(lookup_array) == lookup_array:
# only complain on failures for mode 0 when array is sorted
assert result1 == match(lookup_value, lookup_array, 1)
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]
def test_excel_cmp(lval, op, rval, result):
assert OPERATORS[op](ExcelCmp(lval), rval) == result
def test_excel_cmp(lval, op, rval, expected):
assert OPERATORS[op](ExcelCmp(lval), rval) == expected
