✅ Как сделать Excel VLOOKUP чувствительным к регистру

Содержание

Делаем ВПР чувствительным к регистру

По умолчанию значение подстановки в функции ВПР не чувствительно к регистру. Например, если ваше значение поиска MATT, matt или Matt, то для функции VLOOKUP все равно. Независимо от случая, он вернет первое совпадающее значение.

Делаем ВПР чувствительным к регистру

Предположим, у вас есть данные, как показано ниже:

Как видите, есть три ячейки с одинаковым именем (A2, A4 и A5), но с разным регистром букв. Справа (в E2: F4) у нас есть три имени (Мэтт, Мэтт и Мэтт) вместе с их оценками по математике.

Функция Excel VLOOKUP не поддерживает обработку значений поиска с учетом регистра. В этом примере выше, независимо от того, какой регистр значения поиска (Matt, MATT или matt), он всегда будет возвращать 38 (что является первым совпадающим значением).

В этом руководстве вы узнаете, как сделать ВПР чувствительным к регистру:

Использование вспомогательного столбца.
Без использования вспомогательного столбца и формулы.

Делаем ВПР чувствительным к регистру - Использование вспомогательного столбца

Вспомогательный столбец можно использовать для получения уникального значения поиска для каждого элемента в массиве поиска. Это помогает различать имена с разным регистром букв.

Вот как это сделать:

Вставьте вспомогательный столбец слева от столбца, из которого вы хотите получить данные. В приведенном ниже примере вам нужно вставить вспомогательный столбец между столбцами A и C.
В вспомогательном столбце введите формулу = СТРОКА (). Он вставит номер строки в каждую ячейку.
Используйте следующую формулу в ячейке F2, чтобы получить результат поиска с учетом регистра.
```
= ВПР (МАКС (ТОЧНО (E2; $ A $ 2: $ A $ 9) * (СТРОКА ($ A $ 2: $ A $ 9)))), $ B $ 2: $ C $ 9,2,0)
```
Скопируйте и вставьте его для остальных ячеек (F3 и F4).

Примечание: Поскольку это формула массива, используйте Control + Shift + Enter вместо простого ввода.

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

Давайте разберем формулу, чтобы понять, как она работает:

EXACT (E2; $ A $ 2: $ A $ 9) - эта часть сравнивает значение поиска в E2 со всеми значениями в A2: A9. Он возвращает массив значений ИСТИНА / ЛОЖЬ, где ИСТИНА возвращается при точном совпадении. В этом случае, когда значение в E2 - Matt, он вернет следующий массив:
{ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}.
EXACT (E2; $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9) - эта часть умножает массив ИСТИНА / ЛОЖЬ на номер строки A2: A9. Где бы ни было ИСТИНА, она дает номер строки, иначе - 0. В этом случае возвращается {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9))) - эта часть возвращает максимальное значение из массива чисел. В этом случае он вернет 2 (это номер строки, в которой есть точное совпадение).
Теперь мы просто используем это число в качестве значения поиска и используем массив поиска как B2: C9.

Примечание. Вспомогательный столбец можно вставить в любом месте набора данных. Просто убедитесь, что он находится слева от столбца, из которого вы хотите получить данные. Затем вам необходимо соответствующим образом отрегулировать номер столбца в функции ВПР.

Теперь, если вы не являетесь поклонником вспомогательного столбца, вы также можете выполнять поиск с учетом регистра без вспомогательного столбца.

Делаем ВПР чувствительным к регистру - без вспомогательного столбца

Даже если вы не хотите использовать вспомогательный столбец, вам все равно понадобится виртуальный вспомогательный столбец. Этот виртуальный столбец не является частью рабочего листа, но создается по формуле (как показано ниже).

Вот формула, которая даст вам результат без вспомогательного столбца:

= ВПР (МАКС (ТОЧНО (D2; $ A $ 2: $ A $ 9) * (СТРОКА ($ A $ 2: $ A $ 9))), ВЫБРАТЬ ({1,2}, СТРОКА ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)

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

В формуле также используется концепция вспомогательного столбца. Разница в том, что вместо того, чтобы помещать вспомогательный столбец на рабочий лист, рассматривайте его как виртуальные вспомогательные данные, которые являются частью формулы.

Вот часть, которая работает как вспомогательные данные (выделена оранжевым):

= ВПР (МАКС (ТОЧНО (D2; $ A $ 2: $ A $ 9) * (СТРОКА ($ A $ 2: $ A $ 9))),ВЫБРАТЬ ({1,2}, СТРОКА ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

Позвольте мне показать вам, что я имею в виду под виртуальными вспомогательными данными.

На приведенной выше иллюстрации, когда я выбираю часть формулы ВЫБРАТЬ и нажимаю F9, отображается результат, который даст формула ВЫБРАТЬ.

Результат: {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}.

Это массив, в котором запятая представляет следующую ячейку в той же строке, а точка с запятой означает, что следующие данные находятся в следующей строке. Следовательно, эта формула создает 2 столбца данных: в одном столбце указан номер строки, а в другом - оценка по математике.

Теперь, когда вы используете функцию ВПР, она просто ищет значение подстановки в первом столбце (этих виртуальных данных двух столбцов) и возвращает соответствующий результат. Подстановочное значение здесь - это число, которое мы получаем из комбинации функций MAX и EXACT.

Загрузите файл примера