Общая формула
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Резюме
Поиск с учетом регистра в Excel
По умолчанию при стандартном поиске в Excel регистр не учитывается. И VLOOKUP, и INDEX / MATCH просто вернут первое совпадение, игнорируя регистр.
Прямой способ обойти это ограничение - использовать формулу массива на основе ИНДЕКС / ПОИСКПОЗ с ТЧАСТ. Однако, если вы ищете только числовые значения, SUMPRODUCT + EXACT также предоставляет интересный и гибкий способ поиска с учетом регистра.
В этом примере мы используем следующую формулу
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Хотя эта формула является формулой массива, ее не нужно вводить с помощью Control + Shift + Enter, поскольку SUMPRODUCT изначально обрабатывает массивы.
Объяснение
SUMPRODUCT предназначен для работы с массивами, которые он умножает, а затем суммирует.
В данном случае это два массива с СУММПРОИЗВ: B3: B8 и C3: C8. Хитрость заключается в том, чтобы запустить тест значений в столбце B, а затем преобразовать полученные значения ИСТИНА / ЛОЖЬ в единицы и нули. Мы запускаем тест с EXACT вот так:
EXACT(E3,B3:B8)
Что производит этот массив:
(ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ)
Обратите внимание, что истинное значение в позиции 3 - это наше совпадение. Затем мы используем двойное отрицательное значение (то есть -, которое технически является «двойным унарным») для приведения этих значений ИСТИНА / ЛОЖЬ к 1 и 0. Результатом является следующий массив:
(0; 0; 1; 0; 0; 0)
На этом этапе расчета формула СУММПРОИЗВ выглядит так:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
Затем SUMPRODUCT просто умножает элементы в каждом массиве вместе, чтобы получить окончательный массив:
(0; 0; 775; 0; 0; 0)
Затем СУММПРОИЗВ суммирует и возвращает 775.
Итак, суть этой формулы в том, что значения FALSE используются для отмены всех остальных значений. Выживают только ценности, которые были ИСТИННЫМИ.
Обратите внимание: поскольку мы используем СУММПРОИЗВ, эта формула имеет уникальный поворот: если есть несколько совпадений, СУММПРОИЗВ вернет сумму этих совпадений. Это может быть, а может и не быть тем, что вы хотите, поэтому будьте осторожны, если вы ожидаете нескольких совпадений!
Помните, что эта формула работает только для числовых значений, потому что SUMPRODUCT не обрабатывает текст. Если вы хотите получить текст, используйте INDEX / MATCH + EXACT.
