Общая формула
=INDEX(range1,MATCH(TRUE,EXACT(A1,range2),0))
Резюме
Чтобы выполнить поиск с учетом регистра, вы можете использовать функцию EXACT вместе с INDEX и MATCH. В показанном примере формула в F5:
=INDEX(C5:C14,MATCH(TRUE,EXACT(E5,B5:B14),0))
что возвращает 39, возраст "ДЖИЛЛ СМИТ".
Примечание: это формула массива, и ее необходимо вводить с помощью Control + Shift + Enter, за исключением Excel 365.
Объяснение
По сути, это формула ИНДЕКС и ПОИСКПОЗ с функцией ТОЧНОСТЬ, используемой внутри ПОИСКПОЗ для выполнения сопоставления с учетом регистра.
Работая наизнанку, EXACT настроен на сравнение значения в E5 с именами в диапазоне B5: B14:
EXACT(E5,B5:B14) // returns array of results
Функция EXACT выполняет сравнение с учетом регистра и в результате возвращает TRUE или FALSE. Только значения, которые точно совпадают, вернут ИСТИНА. Поскольку мы проверяем имя в E5 («JILL SMITH») по всем десяти именам в диапазоне B5: B14, мы возвращаем массив из десяти значений ИСТИНА и ЛОЖЬ, например:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE)
Этот массив возвращается непосредственно в функцию ПОИСКПОЗ как lookup_array следующим образом:
MATCH(TRUE,(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE),0)
При поисковом значении ИСТИНА ПОИСКПОЗ возвращает 5, так как единственное ИСТИНА в массиве находится в пятой позиции. Также обратите внимание, что match_type установлено в ноль (0), чтобы обеспечить точное совпадение.
Функция ПОИСКПОЗ возвращает результат непосредственно в функцию ИНДЕКС в виде номера строки, поэтому теперь мы можем переписать формулу следующим образом:
=INDEX(C5:C14,5) // returns 39
ИНДЕКС возвращает возраст в пятой строке, 39, в качестве окончательного результата.
Поскольку использование такой функции EXACT является операцией с массивом, формула является формулой массива и должна вводиться с помощью Control + Shift + Enter, за исключением Excel 365.
С XLOOKUP
В Excel 365 функцию XLOOKUP можно настроить для выполнения поиска с учетом регистра таким же образом:
=XLOOKUP(TRUE,EXACT(J5,B5:B14),C5:C14,"na",0)
Обратите внимание, что значение поиска и массив поиска настроены так же, как функция MATCH выше. После ТОЧНЫХ запусков у нас есть:
=XLOOKUP(TRUE,(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE),C5:C14,"na",0)
а XLOOKUP возвращает 5-й элемент из диапазона C5: C14 (39) в качестве окончательного результата.
