ВПР (VLOOKUP) с учетом регистра

Проблема

Все подстановочные функции Excel из категории Ссылки и массивы (Lookup and Reference), такие как ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH) не учитывают регистр символов (т.е. разницу между прописными и строчными) при поиске данных. Таким образом, при использовании, например, функции ВПР для поиска суммы соответствующей клиенту с кодом Smb3 в данной таблице:

vlookup-with-case1.png

...в итоге получим 38, а не 56 - ибо функция не видит разницы между smb3 и Smb3 и выводит первое встретившееся значение из таблицы.

Что же делать, если нужно находить значение точно, с учетом совпадения регистра символов? Ответ - нужна небольшая формула массива вместо ВПР.

Совет: если вы раньше не особо встречались с формулами массива, то очень рекомендую сходить и почитать вот эту статью, чтобы получить о них общее представление. Если тема знакомая, то читаем дальше.

Решение

Формула, которая нам нужна выглядит следующим образом:

vlookup-with-case2.png

=ИНДЕКС(B4:B9;СУММ(СОВПАД(A4:A9;D4)*СТРОКА(B4:B9))-СТРОКА(B3))

=INDEX(B4:B9,SUM(EXACT(A4:A9,D4)*ROW(B4:B9))-ROW(B3))

Напомню, что в конце ввода этой формулы нужно нажать не привычный Enter, а сочетание Ctrl + Shift + Enter, чтобы ввести ее не как обычную формулу, а как формулу массива. Как видно из примера, эта формула находит правильное значение 56, то есть, фактически, различает строчные и прописные символы в написании кода, в отличие от классической функции ВПР (VLOOKUP).

Как это работает

Чтобы проще и нагляднее объяснить, как именно работает эта формула массива, лучше всего воспользоваться одним из инструментов пошаговой отладки сложных формул в Microsoft Excel - кнопкой Вычислить формулу (Evaluate formula). Найти её можно на вкладке Формулы (Formulas) в группе Зависимости формул (Formula auditing)

В открывшемся окне мы увидим формулу из текущей ячейки и кнопку Вычислить (Calculate), последовательно нажимая на которую, мы будем пошагово рассматривать внутренний механизм расчета нашей формулы:

vlookup-with-case3.png

Итак, поехали - первый этап. Функция СОВПАД (EXACT) проверяет точное совпадение двух текстовых строк с учетом регистра и выдает на выходе ИСТИНА или ЛОЖЬ в зависимости от результата. В нашем случае мы подсунули этой функции не две ячейки для сравнения, как обычно, а массив ячеек (A4:A9), каждая из ячеек которого по очереди будет сравниваться с ячейкой D4, т.е. с нашим поисковым значением. После первого нажатия на кнопку Вычислить мы как раз увидим, как эти данные подставляются в формулу:

vlookup-with-case4.png

После второго нажатия на кнопку Вычислить мы увидим результат сравнения - массив {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}. Видно, что точное совпадение (ИСТИНА) произошло только на пятом элементе массива:

vlookup-with-case5.png

Следующий этап. Функция СТРОКА (ROW) делает совсем простую, по-сути, вещь - выдает номер строки для текущей ячейки. Здесь же мы даем ей в качестве аргумента не одну ячейку, а массив (B4:B9), поэтому получаем на выходе набор номеров строк для каждой ячейки массива, т.е. {4;5;6;7;8;9}:

vlookup-with-case6.png

Затем эти два массива попарно умножаются друг на друга, давая нам на выходе массив {0;0;0;0;8;0}, т.к. ЛОЖЬ (FALSE) в понимании Excel равносильно нулю, а ИСТИНА (TRUE) - единице:

vlookup-with-case7.png

Чтобы получить порядковый номер строки с нужными нам данными внутри таблицы - вычитаем из номера строки на листе (8) номер строки начала таблицы, который определяет функция СТРОКА(B3):

vlookup-with-case8.png

Таким образом, мы получаем в итоге число 5 - номер строки в нашей таблице, где находится точное совпадение с искомым значением из D4 с учетом регистра. Осталось извлечь данные из нужной ячейки столбца по вычисленному номеру строки. Это делает функция ИНДЕКС (INDEX), первый аргумент которой - это массив наших значений (B4:B9), а второй - номер строки в этом массиве (5), из которой мы хотим извлечь нужное нам значение:

vlookup-with-case9.png

Ссылки по теме

 


Фёдор
24.10.2012 23:52:42
Офигенски!
И как они такое придумывают? ©SouthPark.
Борис
24.10.2012 23:53:11
Спасибо Николай!
Молодец!!!!
Максим
24.10.2012 23:53:43
Реально круто! Респект!
Максим
24.10.2012 23:54:35
Функция начинает работать некорректно если в диапазоне два или более Smb3 - точных совпадения.
Я бы советовал использовать
 =ИНДЕКС(B4:B9;ПОИСКПОЗ(ИСТИНА();СОВПАД(A4:A9;D4);0))
Дмитрий
24.10.2012 23:55:28
Если кто не любит формулы массива, то можно простой формулой:
=ПРОСМОТР(2;1/СОВПАД(A4:A9;D4);B4:B9)
29.07.2013 08:37:38
классно, но можно по-моему сократить формулу массива до =SUM(EXACT(A4:A9;D4)*(B4:B9)) для данного примера, не считаете? Работает также......
04.08.2013 16:42:07
Если во втором столбце только числа, то да. А если там текст?
27.02.2014 00:31:21
Николай здравствуйте!  
Не могли бы подсказать, а как найти данной формулой массива сумму по массиву ячеек, к примеру {=ИНДЕКС(B4:B9;СУММ(СОВПАД(A4:A9;D9:D14)))} где D9:D14 это массив А4:A9 из примера выше.
14.06.2014 12:43:29
Кто знает, как можно сделать так, чтобы строка в таблице копировалась в конец таблицы при выполнении 2-ух условий. Причем после копирования в исходной строке данные в определенных ячейках менялись по условию, а в новой скопированной строке были заполнены строки выборочно только из определенных ячеек старого? Буду очень признателен за подсказку. И вообще возможно ли такое сделать в excel?
14.06.2014 12:48:18
А как этот вопрос связан с темой статьи? Тут макросы нужны будут, скорее всего.
С такими задачами лучше на Форум.
MCH
17.03.2017 08:31:55
Варианты формул
формула массива:
=ИНДЕКС(B4:B9;ПОИСКПОЗ(ИСТИНА;СОВПАД(D4;A4:A9);0))

немассивная формула:
=ПРОСМОТР(2;1/СОВПАД(A4:A9;D4);B4:B9)

Работают немного по разному, первая формула вернет первое совпадение, вторая - последнее
Если значения в столбце A уникальны, то результат совпадет
14.02.2018 08:13:52
Зачем в формуле ПРОСМОТР первый аргумент 2? Пробовал с 1 так же работала. В чем сакральный смысл?
MCH
14.02.2018 09:04:54
Число 2 гарантировано больше, чем может быть при вычислении выражения 1/СОВПАД(A4:A9;D4), поэтому будет найден корректный результат, при поиске единицы результат может быть не верным
14.02.2018 09:05:23
спасибо!
Наверх