Здравствуйте, друзья. Когда мы ищем ячейку по ее содержимому – используем функцию ВПР, или комбинацию ПОИСКПОЗ и ИНДЕКС. Оба варианта хорошо справляются с поиском, но только если нам не важен регистр символов искомой строки.

ВПР и регистр

Смотрите на примере. Здесь есть 3 личности с похожими именами. С помощью ВПР мы ищем строку «Сергей» и должны получить его возраст (50 лет). Но из-за того, что ВПР не различает регистры символов, мы получили другой результат. И это проблема. В этой статье я расскажу, как найти значение с учетом строчных и заглавных букв. Будем использовать функции ИНДЕКС, ПОИСКПОЗ и СОВПАД.

Забегая вперед, скажу, что готовая формула выглядит так:

{=ИНДЕКС( данные ; ПОИСКПОЗ(ИСТИНА ; СОВПАД( значение ; диапазон ) ; 0) ; колонка_для_вывода )}

Здесь используется формула массива. После её ввода нужно нажать не Enter, а Ctrl+Shift+Enter. В противном случае, работать не будет. Фигурные скобки вначале и в конце появятся автоматически, печатать их не нужно!

Формула выглядит немного запутанно, поэтому давайте пошагово разберемся с последовательностью её работы:

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

    В массиве будет столько элементов, сколько их в диапазоне с именами. В тех элементах массива, где имена точно совпадают, будет записана ИСТИНА, в остальных – ЛОЖЬ;

  2. Теперь функция ПОИСКПОЗ найдет в этом массиве порядковый номер первого истинного элемента. Используем поиск по точному совпадению: ={ПОИСКПОЗ(ИСТИНА ; СОВПАД( значение ; диапазон ) ; 0)}.

    В результате такой операции мы получим число 3, т.е. в третьем элементе массива содержится ИСТИНА

  3. Остается вывести элемент по его найденному порядковому номеру, указав, что нужна вторая колонка. Используем функцию ИНДЕКС: {=ИНДЕКС( данные ; ПОИСКПОЗ(ИСТИНА ; СОВПАД( значение ; диапазон ) ; 0) ; колонка_для_вывода )}

ПОИСКПОЗ с учетом регистра

Теперь мы получили правильный результат. Не смотря на громоздкость формулы, это не самая сложная операция, которую приходилось делать в Excel. Если Вам понятно, как это работает – попрактикуйтесь на своих примерах для закрепления материала. Если не понятно – жду ваши вопросы в комментариях.

А у меня всё, увидимся на страницах officelegko.com!