Поиск следующего большего совпадения с помощью ИНДЕКС и ПОИСКПОЗ

Чтобы найти следующее большее совпадающее число (то есть наименьшее значение, которое больше или равно заданному) в диапазоне числовых значений Excel, используйте формулу на основе функций ИНДЕКС и ПОИСКПОЗ с типом совпадения –1.

Как найти следующее большее подходящее число?

Чтобы получить следующее большее совпадение для 68666 из числового диапазона, показанного выше, используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ с -1 в качестве типа совпадения. Тип совпадения -1 заставит функцию ПОИСКПОЗ найти наименьшее значение, которое больше или равно 68666. Обратите внимание: значения в числовом диапазоне должны быть расположены в порядке убывания. Поэтому, если значения в вашем диапазоне расположены в другом поряд ке, отсортируйте их по убыванию согласно инструкциям здесь. Только в этом случае функция ПОИСКПОЗ сможет корректно определить следующее большее совпадение. После этого функция ИНДЕКС извлечёт значение по позиции, переданной функцией ПОИСКПОЗ.

Общий синтаксис

=INDEX(return_range,MATCH(lookup_value,lookup_array,-1))

Чтобы получить следующее большее совпадение для 68666, скопируйте или введите приведённые ниже формулы в ячейку E6 и нажмите Enter, чтобы получить результат:

=ИНДЕКС()B5:B14,ПОИСКПОЗ()68666,B5:B14,-1))

Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=ИНДЕКС()B5:B14,ПОИСКПОЗ()E5,B5:B14,-1))

Пояснение формулы

=INDEX(B5:B14,MATCH(E5,B5:B14,-1))

• MATCH(E5,B5:B14,-1): Функция ПОИСКПОЗ с типом совпадения -1 находит позицию наименьшего значения, которое больше или равно 68666 — то есть значению в ячейке E5 — в диапазоне B5:B14. Таким образом, функция ПОИСКПОЗ определит число 75681 и вернёт 3, поскольку это число находится на 3-й позиции диапазона.
• ИНДЕКС()B5:B14,MATCH(E5,B5:B14,-1)) = ИНДЕКС(B5:B14 Функция ИНДЕКС возвращает 3-е значение из диапазона B5:B14, то есть 75681.

Связанные функции

Связанные формулы