Как выполнить ВПР с учётом или без учёта регистра в Excel?
Предположим, у вас есть диапазон данных, как показано на снимке экрана ниже, и вы хотите найти цену товара VA-6. При использовании стандартной формулы поиска вы получите цену первого совпадающего значения без учёта регистра. В этом руководстве описаны методы выполнения ВПР с учётом или без учёта регистра в Excel.
Поиск без учёта регистра с помощью формулы ВПР
Поиск без учёта регистра с помощью Kutools для Excel 
Поиск С учетом регистра с помощью функции СУММПРОИЗВ
Поиск С учетом регистра с помощью пользовательской функции
Поиск без учёта регистра с помощью формулы ВПР
Для выполнения ВПР значения на основе другого значения без учёта регистра достаточно всего одной формулы ВПР.
Выберите пустую ячейку, куда будет помещён найденный результат, введите формулу =VLOOKUP(F1,$A$2:$C$7,3,FALSE) и нажмите клавишу Enter, чтобы получить первое совпадающее значение.
Совет: В формуле F1 — это искомое значение, A2:C7 — диапазон таблицы, а 3 указывает номер столбца, из которого нужно извлечь совпадающее значение. Вы можете настроить эти параметры под свои задачи.
Поиск без учёта регистра с помощью Kutools для Excel
Если у вас установлена надстройка Kutools для Excel, вы можете воспользоваться функцией Найти данные в диапазоне, чтобы быстро и легко находить значения без учёта регистра.
После бесплатной установкиKutools для Excel выполните следующие действия:
1. Выберите пустую ячейку для размещения результата поиска и нажмите Kutools > Помощник формул > Помощник формул. См. снимок экрана:
2. В диалоговом окне Помощник формул выберите Найти значение в списке раздела Выберите формулу, затем перейдите к правой части окна и укажите диапазон таблицы, искомое значение и столбец, из которого нужно вернуть данные. См. снимок экрана:
3. Нажмите кнопку ОК — и совпадающее значение будет возвращено.
Поиск С учетом регистра с помощью функции СУММПРОИЗВ
Если вам нужно найти значение с учётом регистра, воспользуйтесь функцией СУММПРОИЗВ.
Выберите пустую ячейку для размещения результата поиска и введите формулу =SUMPRODUCT((EXACT($A$2:$A$7,$F$1)*($C$2:$C$7))), затем нажмите клавишу Enter, чтобы получить совпадающее значение. См. снимок экрана:
Совет: В формуле $A$2:$A$7 — это столбец, в котором выполняется поиск искомого значения, F1 — само искомое значение, а $C$2:$C$7 — столбец, из которого следует извлечь соответствующее значение. Вы можете изменить эти параметры в соответствии со своими задачами.
Поиск С учетом регистра с помощью пользовательской функции
Если вы предпочитаете использовать пользовательскую функцию, вот одна из них, которая отлично справится с этой задачей.
1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Щелкните Вставка > Модуль и вставьте приведённый ниже код VBA в новое окно Модуля.
VBA: поиск значения С учетом регистра
Function CaseVLook(FindValue, TableArray As Range, Optional ColumnID As Integer = 1) As Variant
Dim xCell As Range
Application.Volatile
CaseVLook = "Not Found"
For Each xCell In TableArray.Columns(1).Cells
If xCell = FindValue Then
CaseVLook = xCell.Offset(0, ColumnID - 1)
Exit For
End If
Next
End Function3. Сохраните код, вернитесь на активный лист, выберите пустую ячейку для размещения результата поиска, введите формулу =CaseVLook(F1,A1:C7,3) и нажмите клавишу Enter, чтобы получить результат. См. снимок экрана:
Совет:В формуле F1 — это искомое значение, A2:C7 — диапазон таблицы, а 3 указывает номер столбца, из которого следует извлечь совпадающее значение. Вы можете изменить эти параметры в соответствии со своими требованиями.
Связанные статьи:
- Как выполнить ВПР и получить несколько соответствующих значений по горизонтали в Excel?
- Как с помощью функции ВПР найти наименьшее значение и вернуть соответствующую соседнюю ячейку в Excel?
- Как использовать ВПР со списком выбора в Excel?
- Как найти значение и получить адрес соответствующей ячейки в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек