Загальна формула
(=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num))
Резюме
Пошук з урахуванням регістру
За замовчуванням стандартні пошукові запити з VLOOKUP або INDEX + MATCH не чутливі до регістру. І VLOOKUP, і MATCH просто повернуть перший матч, ігноруючи регістр.
Однак, якщо вам потрібно виконати пошук з урахуванням регістру, ви можете зробити це за допомогою формули масиву, що використовує функції INDEX, MATCH та EXACT.
У прикладі ми використовуємо наступну формулу
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Ця формула є формулою масиву і її потрібно вводити за допомогою Control + Shift + Enter.
Пояснення
Оскільки сам MATCH не чутливий до регістру, нам потрібен спосіб отримати Excel для порівняння регістру. Функція EXACT є ідеальною функцією для цього, але те, як ми її використовуємо, є трохи незвичним, оскільки нам потрібно порівняти одну клітинку з діапазоном комірок.
Працюючи зсередини, ми маємо спочатку:
EXACT(F4,B3:B102)
де F4 містить значення пошуку, а B3: B102 - посилання на стовпець пошуку (Імена). Оскільки ми надаємо EXACT масив як другий аргумент, ми повернемо масив з TRUE хибних значень, як це:
(FALSE, FALSE, FALSE, FALSE, FALSE, TRUE тощо)
Це результат порівняння значення в B4 кожної комірки стовпця підстановки. Скрізь, де ми бачимо TRUE, ми знаємо, що ми точно відповідаємо справі.
Тепер нам потрібно отримати позицію (тобто номер рядка) значення TRUE у цьому масиві. Для цього ми можемо використовувати MATCH, шукаючи TRUE і встановлювати в режимі точної відповідності:
MATCH(TRUE,EXACT(F4,B3:B102),0)
Важливо зазначити, що MATCH завжди повертає перший збіг, якщо є дублікати, тому, якщо в стовпці трапляється інший точний збіг, ви збігатимете лише перший.
Тепер у нас є номер рядка. Далі нам просто потрібно використовувати INDEX, щоб отримати значення в правому перетині рядків і стовпців. Номер стовпця в цьому випадку закодовано як 3, оскільки названі дані діапазону включають усі стовпці. Остаточна формула:
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Ми маємо ввести цю формулу як формулу масиву через масив, створений EXACT.
Ця формула отримає як текстові, так і числові значення. Якщо ви хочете отримати лише цифри, ви можете скористатися формулою на основі SUMPRODUCT; дивіться посилання нижче
