(Ответ здесь как отдельный вопрос для отсортированных данных.)
Если сортировала данные, вы можете использовать VLOOKUP с range_lookup аргументом TRUE (или опущен, так как это по умолчанию), который официально описан для Excel , как "поиск приблизительного матча".
Другими словами, для отсортированных данных:
- установка последнего аргумента в
FALSE возвращает первое значение, и
- установка последнего аргумента в
TRUE возвращает последнее значение.
Это в значительной степени недокументировано и неясно, но датируется VisiCalc (1979), и на сегодняшний день действует по крайней мере в Microsoft Excel, LibreOffice Calc и Google Sheets. В конечном счете, это связано с первоначальной реализацией LOOKUP в VisiCalc (а затем и с VLOOKUP и HLOOKUP), когда не было четвертого параметра. Значение определяется с помощью бинарного поиска с использованием включающей левой границы и исключительной правой границы (распространенная и элегантная реализация), что приводит к такому поведению.
Технически это означает, что поиск начинается с интервала-кандидата [0, n) , где n - длина массива, а условие инварианта цикла - A[imin] <= key && key < A[imax] ( левая граница <= цель, правая граница, которая начинается одна после конца, является> целью; для проверки либо проверки значений в конечных точках до, либо проверки результата после) и последовательного деления пополам и выбора той стороны, которая сохраняет этот инвариант : путем исключения одна сторона будет, пока вы не дойдете до интервала с 1 членом, [k, k+1) , а затем алгоритм возвращает k . Это не обязательно должно быть точное совпадение (!): Это просто самое близкое совпадение снизу. В случае дублирующих совпадений это приводит к возвращению последнего совпадения, так как требует, чтобы следующее значение было больше ключа (или конца массива). В случае дубликатов вам нужно некоторое поведение, и это разумно и легко реализовать.
Это поведение явно указано в этой старой статье базы знаний Майкрософт (выделение добавлено): «XL: Как вернуть первое или последнее совпадение в массиве» (Q214069):
Вы можете использовать функцию LOOKUP() для поиска значения в массиве отсортированных данных и возврата соответствующего значения, содержащегося в этой позиции, в другом массиве. Если значение поиска повторяется в массиве, оно возвращает последнее найденное совпадение. Такое поведение верно для функций VLOOKUP(), HLOOKUP() и LOOKUP().
Официальная документация для некоторых таблиц приведена ниже; ни в одном из них не указано поведение "последнее совпадение", но оно подразумевается в документации Google Sheets:
Майкрософт Эксель
TRUE предполагает, что первый столбец в таблице отсортирован по номерам или по алфавиту, а затем будет искать ближайшее значение.
Google Sheets:
Если is_sorted равно TRUE или опущено, возвращается ближайшее совпадение (меньше или равно ключу поиска)
