Резюме
Для поиска данных на основе нескольких сложных критериев вы можете использовать функцию XLOOKUP с несколькими выражениями на основе логической логики. В показанном примере формула в G5:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
С настройками XLOOKUP по умолчанию для режима совпадения (точное) и режима поиска (от первого до последнего) формула соответствует первой записи, где:
счет начинается с "x" И регион "восток", а месяц НЕ апрель.
что является четвертой записью (строка 8) в показанном примере.
Объяснение
Обычно функция XLOOKUP настроена на поиск значения в поисковом массиве, который существует на листе. Однако, когда критерии, используемые для сопоставления значения, становятся более сложными, вы можете использовать логическую логику для создания на лету поискового массива, состоящего только из единиц и нулей, а затем искать значение 1. В этом примере используется такой подход:
=XLOOKUP(1,boolean_array,result_array)
В этом примере требуются следующие критерии:
счет начинается с "x" И регион "восток", а месяц НЕ апрель.
Для каждого из трех отдельных критериев выше мы используем отдельное логическое выражение. Первое выражение использует функцию LEFT, чтобы проверить, начинается ли Account с "x":
LEFT(B5:B16)="x" // account begins with "x"
Поскольку мы проверяем двенадцать значений, результатом является массив с двенадцатью значениями, подобными этому:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Второе выражение проверяет, является ли регион "восточным" с помощью оператора равенства (=):
C5:C16="east" // region is east
Как и раньше, мы получаем еще один массив с двенадцатью значениями ИСТИНА ЛОЖЬ:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Из третьего выражения нужно исключить апрель. Самый простой способ сделать это - протестировать апрель месяц напрямую с помощью функции МЕСЯЦ:
MONTH(D5:D16)=4 // month is April
Затем используйте функцию НЕ, чтобы отменить результат:
NOT(MONTH(D5:D16)=4) // month is not April
который создает массив, корректно описывающий "не апрель":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Затем все три массива умножаются вместе, и математическая операция переводит значения ИСТИНА и ЛОЖЬ в 1 и 0:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
В булевой арифметике умножение работает как логическая функция И, поэтому конечный результат представляет собой один массив, подобный этому:
(0;0;0;1;1;0;0;0;0;1;0;1)
Формулу теперь можно переписать так:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
С 1 в качестве значения поиска и настройками по умолчанию для режима совпадения (точное) и режима поиска (от первого до последнего) XLOOKUP соответствует первой 1 (четвертая позиция) и возвращает соответствующую строку в массиве результатов, то есть B8: E8.
Последний матч
Установив необязательный аргумент режима поиска на -1, вы можете найти «последнее совпадение» по тем же критериям, как это:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
