Общая формула
=XLOOKUP(1,--EXACT(range1,"RED"),range2)
Резюме
Чтобы создать точное соответствие с учетом регистра, вы можете использовать функцию XLOOKUP с функцией EXACT. В показанном примере формула в F5:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15)
который соответствует "RED" (с учетом регистра) и возвращает всю строку.
Объяснение
Сама по себе функция XLOOKUP не чувствительна к регистру. Значение поиска «RED» будет соответствовать «красный», «RED» или «Red». Мы можем обойти это ограничение, построив подходящий поисковый массив для XLOOKUP с логическим выражением.
Работая изнутри, чтобы дать XLOOKUP возможность сопоставлять регистр, мы используем функцию EXACT следующим образом:
EXACT(B5:B15,"RED") // test for "RED"
Поскольку существует 11 значений в диапазоне E5: D15, EXACT возвращает массив с 11 результатами TRUE FALSE следующим образом:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Обратите внимание, что положение ИСТИНА соответствует строке, в которой цвет «КРАСНЫЙ».
Для краткости (и для того, чтобы логика могла быть легко расширена с помощью логической логики), мы устанавливаем значения ИСТИНА ЛОЖЬ до 1 и 0 с двойным отрицанием:
--EXACT(B5:B15,"RED") // convert to 1s and 0s
что дает такой массив:
(0;0;0;0;1;0;0;0;0;0;0)
Обратите внимание, что позиция 1 соответствует строке, в которой цвет «КРАСНЫЙ». Этот массив возвращается непосредственно в функцию XLOOKUP в качестве аргумента массива поиска.
Теперь мы можем просто формулу:
=XLOOKUP(1,(0;0;0;0;1;0;0;0;0;0;0),B5:D15)
При поисковом значении 1 XLOOKUP находит 1 в 5-й позиции и возвращает 5-ю строку в возвращаемом массиве B9: D9.
Расширяя логику
Структура логики легко расширяется. Например, чтобы сузить соответствие до «КРАСНЫЙ» в апреле месяце, вы можете использовать следующую формулу:
=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)
Здесь, поскольку каждое из двух выражений возвращает массив значений ИСТИНА ЛОЖЬ, и поскольку эти массивы умножаются вместе, математическая операция переводит значения ИСТИНА и ЛОЖЬ в 1 и 0. Необязательно использовать двойное отрицание.
Поскольку значение поиска остается 1, как в приведенной выше формуле.
Первый и последний матч
Обе приведенные выше формулы вернут первое совпадение "КРАСНОГО" в наборе данных. Если вам нужно последнее совпадение, вы можете выполнить обратный поиск, установив аргумент режима поиска для XLOOKUP равным -1:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match
Если вам нужно вернуть результаты из нескольких совпадений, см. Функцию ФИЛЬТР.
