Общая формула
=XLOOKUP(max,dates,results,,-1) // latest match by date
Резюме
Чтобы получить последнее совпадение в наборе данных по дате, вы можете использовать XLOOKUP в режиме приблизительного совпадения, задав для match_mode значение -1. В показанном примере формула в G5, скопированная ниже, имеет следующий вид:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
где дата (C5: C15), позиция (B5: B15) и цена (D5: D15) являются именованными диапазонами.
Объяснение
XLOOKUP предлагает несколько функций, которые делают его исключительно полезным для более сложных поисков. В этом примере нам нужна последняя цена на товар по дате. Если бы данные были отсортированы по дате в порядке возрастания, это было бы очень просто. Однако в этом случае данные не отсортированы.
По умолчанию XLOOKUP возвращает первое совпадение в наборе данных. Чтобы получить последнее совпадение, мы можем установить для необязательного аргумента search_mode значение -1, чтобы XLOOKUP выполнял поиск «от последнего к первому». Однако мы не можем использовать этот подход здесь, потому что нет гарантии, что последняя цена на товар появится последней.
Вместо этого мы можем установить необязательный аргумент match_mode равным -1, чтобы обеспечить приблизительное совпадение «точное или следующее наименьшее», и настроить значение поиска и массив поиска, как описано ниже. Скопированная формула в G5:
=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)
Работая с аргументами один за другим, lookup_value является самой большой (самой последней) датой в данных:
MAX(date) // get max date value
Lookup_array выводится с помощью логического выражения:
(item=F5)*date
Сравнивая каждый элемент со значением в F5 «Пояс», мы получаем массив значений ИСТИНА / ЛОЖЬ:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE)
где значения ИСТИНА представляют записи для «Пояса». Этот массив действует как фильтр. Когда он умножается на значения в названном диапазоне дат , значения ИСТИНА / ЛОЖЬ оцениваются как 1 и 0:
=(1;0;0;0;0;0;1;0;1;0;0)*date
Результатом является массив, содержащий только нули и даты для ремней:
=(43484;0;0;0;0;0;43561;0;43671;0;0)
Примечание: серийные номера являются действительными датами Excel.
Этот массив доставляется непосредственно в XLOOKUP в качестве аргумента lookup_array.
Return_array - это цена именованного диапазона (D5: D15)
Необязательный аргумент not_found не указан.
Match_mode устанавливается в -1, для точного совпадения или следующего наименьшего элемента.
XLOOKUP просматривает массив поиска максимального значения даты. Поскольку массив уже был отфильтрован, чтобы исключить даты, не связанные с «поясом», XLOOKUP просто находит наилучшее совпадение (либо точную дату, либо следующую наименьшую дату), которое соответствует последней дате.
Конечный результат - цена, связанная с последней датой. Формула продолжит работать, если данные отсортированы в любом порядке.
