Загальна формула
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Резюме
Пошук з урахуванням регістру в Excel
За замовчуванням стандартні пошуки в Excel не чутливі до регістру. І VLOOKUP, і INDEX / MATCH просто повернуть перший збіг, ігноруючи регістр.
Прямим способом вирішення цього обмеження є використання формули масиву на основі INDEX / MATCH з EXACT. Однак, якщо ви шукаєте лише числові значення, SUMPRODUCT + EXACT також надає цікавий та гнучкий спосіб пошуку з урахуванням регістру.
У прикладі ми використовуємо наступну формулу
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Хоча ця формула є формулою масиву, її не потрібно вводити за допомогою Control + Shift + Enter, оскільки SUMPRODUCT обробляє масиви спочатку.
Пояснення
SUMPRODUCT призначений для роботи з масивами, які він множить, потім підсумовує.
У цьому випадку ми представляємо два масиви з SUMPRODUCT: B3: B8 і C3: C8. Фокус полягає у проведенні тесту на значення у стовпці B, а потім перетворення отриманих значень TRUE / FALSE на 1 та 0. Ми запускаємо тест з EXACT приблизно так:
EXACT(E3,B3:B8)
Що створює цей масив:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Зверніть увагу, що справжнє значення в позиції 3 - це наш збіг. Потім ми використовуємо подвійний негатив (тобто -, що технічно є "подвійним одинарним"), щоб привести ці значення TRUE / FALSE до 1 і 0. Результатом є такий масив:
(0; 0; 1; 0; 0; 0)
На даний момент розрахунку формула SUMPRODUCT виглядає так:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
Потім SUMPRODUCT просто множить елементи в кожному масиві разом, щоб отримати кінцевий масив:
(0; 0; 775; 0; 0; 0)
Який SUMPRODUCT потім підсумовує, і повертає 775.
Отже, суть цієї формули полягає в тому, що значення FALSE використовуються для скасування всіх інших значень. Єдині цінності, які виживають, це ті, які були ІСТИННИМИ.
Зауважте, що оскільки ми використовуємо SUMPRODUCT, ця формула має унікальний варіант: якщо декілька збігів, SUMPRODUCT поверне суму цих збігів. Це може бути чи не тим, що ви хочете, тому будьте обережні, якщо ви очікуєте декількох збігів!
Пам’ятайте, ця формула працює лише для числових значень, оскільки SUMPRODUCT не обробляє текст. Якщо ви хочете отримати текст, використовуйте INDEX / MATCH + EXACT.
