Обща формула
=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.
Обяснение
В основата си това е формула INDEX и MATCH, с функцията EXACT, използвана вътре в MATCH за изпълнение на мач, чувствителен към малки и големи букви.
Работейки отвътре навън, EXACT е конфигуриран да сравнява стойността в E5 с имена в диапазона B5: B14:
EXACT(E5,B5:B14) // returns array of results
Функцията EXACT изпълнява сравнение с малки и големи букви и в резултат връща TRUE или FALSE. Само стойности, които съвпадат точно, ще върнат TRUE. Тъй като проверяваме името в E5 ("JILL SMITH") спрямо всичките десет имена в диапазона B5: B14, получаваме обратно масив от десет TRUE и FALSE стойности като тази:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE)
Този масив се връща директно към функцията MATCH като lookup_array по следния начин:
MATCH(TRUE,(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE),0)
Със справочна стойност TRUE, MATCH връща 5, тъй като единствената TRUE в масива е на петата позиция. Забележете също, че match_type е зададен на нула (0), за да принуди точно съвпадение.
Функцията MATCH връща резултат директно към функцията INDEX като номер на ред, така че вече можем да пренапишем формулата по следния начин:
=INDEX(C5:C14,5) // returns 39
INDEX връща възрастта на петия ред, 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) като краен резултат.
