Общая формула
=INDEX(range1,MATCH(lookup,range2,-1))
Резюме
Чтобы получить значения из таблицы, в которой значения поиска отсортированы в порядке убывания (ZA), вы можете использовать ИНДЕКС и ПОИСКПОЗ, при этом ПОИСКПОЗ настроено для приблизительного совпадения с использованием типа соответствия -1. В показанном примере формула в F5:
=INDEX(C5:C9,MATCH(F4,B5:B9,-1))
где значения в B5: B9 отсортированы в порядке убывания.
Контекст
Предположим, у вас есть продукт, который продается в рулонах по 100 футов, и заказы разрешены только целыми рулонами. Например, если вам нужно 200 футов материала, вам понадобится всего два рулона, а если вам нужно 275 футов, вам нужно будет купить три рулона. В этом случае вы хотите, чтобы формула возвращала «следующий высший» уровень всякий раз, когда вы переходите через четное кратное 100.
Объяснение
В этой формуле используется -1 для типа соответствия, чтобы обеспечить приблизительное совпадение значений, отсортированных в порядке убывания. Часть формулы MATCH выглядит так:
MATCH(F4,B5:B9,-1)
Используя значение поиска в ячейке F4, ПОИСКПОЗ находит первое значение в B5: B9, которое больше или равно значению поиска. Если найдено точное совпадение, ПОИСКПОЗ возвращает относительный номер строки для этого совпадения. Если точное совпадение не найдено, ПОИСКПОЗ перебирает значения в B5: B9 до тех пор, пока не будет найдено меньшее значение, затем он «отступает» и возвращает номер предыдущей строки.
В показанном примере значение поиска - 275, поэтому ПОИСКПОЗ возвращает номер строки 3 в ИНДЕКС:
=INDEX(C5:C9,3)
Затем функция ИНДЕКС возвращает третье значение в диапазоне C5: C9, которое составляет 383 доллара США.
