Формула Excel: пошук з урахуванням регістру -

Загальна формула

=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 як масив пошуку:

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) як остаточний результат.

Цікаві статті...