Как найти значение в другой таблице или сила ВПР

Если в двух словах, то ВПР позволяет сравнить данные двух таблиц на основании значений из одного столбца. Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Возьмем две таблицы: рис.1 На картинке выше для удобства они показаны рядом, но на самом деле могут быть расположены на разных листах. Таблицы по сути одинаковые, но фамилии в них расположены в разном порядке, и к тому же в одной заполнены все столбцы, а во второй столбцы ФИО и Отдел. И из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать - все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Вот где ВПР (VLOOKUP) будет весьма кстати. Все, что необходимо - записать в ячейку C2 второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу: =ВПР( $A2 ; Лист1!$A$1:$C$4 ;3;0) =VLOOKUP($A2,Лист1!$A$1:$C$4,3,0) Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций, выбрав в категории Ссылки и массивы (References & Arrays) функцию ВПР (VLOOKUP) и по отдельности указав нужные критерии. Теперь копируем( Ctrl + C ) ячейку с формулой(С2), выделяем все ячейки столбца С до конца данных и вставляем( Ctrl + V ).

Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности. ВПР ищет заданное нами значение(аргумент искомое_значение ) в первом столбце указанного диапазона(аргумент таблица ). Поиск значения всегда происходит сверху вниз(собственно, поэтому функция и называется ВПР: В ертикальный ПР осмотр). Как только функция находит заданное значение - поиск прекращается, ВПР берет строку с найденным значением и смотрит на аргумент номер_столбца . Именно из этого столбца берётся значение, которое мы и видим как итог работы функции. Т.е. в нашем конкретном случае, для ячейки С2 второй таблицы, функция берет фамилию "Петров С.А." (ячейка $A2 второй таблицы) и ищет её в первом столбце указанной таблицы( Лист1!$A$1:$C$4 ), т.е. в столбце А. Как только находит(это ячейка А3)

ВПР может вернуть только одно значений - первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице), то результатом функции будет ошибка #Н/Д (#N/A) . Не надо этого бояться - это даже полезно. Вы точно будете знать, каких записей нет и таким образом можете сравнивать две таблицы друг с другом. Иногда получается так, что Вы видите: данные есть в обеих таблицах, но ВПР выдает #Н/Д. Значит данные в Ваших таблицах не идентичны. В какой-то из них есть лишние неприметные пробелы(обычно перед значением или после), либо знаки кириллицы перемешаны со знаками латиницы. Так же #Н/Д будет, если критерии числа и в искомой таблице они записаны как текст(как правило в левом верхнем углу такой ячейки появляется зеленый треугольничек), а в итоговой - как числа. Или наоборот.

Описание аргументов ВПР

Искомое_значение ( $A2 ) - это то значение из одной таблицы, которые мы ищем в другой таблице. Т.е. для первой записи второй таблицы это будет Петров С.А. . Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках - =ВПР( "Петров С.А" ;Лист1!$A$1:$C$4;3;0) , либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки: "*" и "?" . Это очень удобно, если необходимо найти значения лишь по части строки. Например, можно не вводить полностью "Петров С.А", а ввести лишь фамилию и знак звездочки - "Петров*". Тогда будет выведена любая запись, которая начинается на "Петров". Если же надо найти запись, в которой в любом месте строки встречается фамилия "Петров" , то можно указать так: "*петров*" . Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: "Иванов . " . Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A2 записано: Иванов . Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A2 &"*" . Эта запись будет равнозначна "Иванов*" . В A2 записано Иванов , амперсанд( & ) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем: A2&"*" =>"Иванов"&"*" =>"Иванов*" А полная формула в итоге будет выглядеть так: =ВПР( A2&"*" ; Лист1!$A$1:$C$4 ; 3 ;0) Очень удобно, если значений для поиска много. Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: "*"& A1 &"*"
Таблица( Лист1!$A$1:$C$4 ) - указывается диапазон ячеек, в первом столбце которых будет просматриваться аргумент Искомое_значение . Диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100 . Диапазон в аргументе таблица всегда должен быть "закреплен" , т.е. содержать знаки доллара( $ ) перед названием столбцов и перед номерами строк( Лист1! $ A $ 1: $ C $ 4 ).
Номер_столбца(3) - указывается номер столбца в аргументе Таблица , значения из которого нам необходимо записать в итоговую ячейку в качестве результата. В примере это Дата принятия - т.е. столбец №3. Если бы нужен был отдел, то необходимо было бы указать номер столбца 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Номер столбца всегда указывается числом и не должен быть больше числа столбцов в аргументе Таблица .

если аргумент Таблица имеет слишком большое кол-во столбцов и необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их количество. Можно использовать формулу, которая подсчитывает количество столбцов в указанном диапазоне: =ВПР( $A2 ;Лист1! $A$1:$C$4 ;ЧИСЛСТОЛБ(Лист1! $A$1:$C$4 );0) . К слову в данном случае Лист1! тоже можно убрать, т.к. функция ЧИСЛОСТОЛБ просто подсчитывает количество столбцов в переданном ей диапазоне и неважно на каком он листе: =ВПР( $A2 ;Лист1! $A$1:$C$4 ;ЧИСЛСТОЛБ( $A$1:$C$4 );0) .

При работе с ВПР всегда важно помнить три вещи:

Таблица всегда должна начинаться с того столбца, в котором ищем Искомое_значение . Т.е. ВПР не умеет искать значение во втором столбце таблицы, а значение возвращать из первого. В лучшем случае ничего найдено не будет и получим ошибку #Н/Д (#N/A) , а в худшем результат будет совсем не тот, который должен быть
аргумент Таблица должен быть "закреплен" , т.е. содержать знаки доллара( $ ) перед названием столбцов и перед номерами строк( Лист1! $ A $ 1: $ C $ 4 ). Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон). Как это делается. Выделяете текст ссылки и жмете клавишу F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы из одной ячейки в остальные аргумент Таблица будет "съезжать" и результат может быть совсем не таким, какой ожидался(в лучшем случае получите ошибку #Н/Д (#N/A)
номер_столбца не должен превышать общее кол-во столбцов в аргументе таблица , а сама Таблица соответственно должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере указана Лист1!$A$1:$C$4 - всего 3 столбца(A, B, C). Значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца. Т.е. если мы запишем формулу так: =ВПР( $A2 ; Лист1!$A$1:$C$4 ; 4 ;0) - мы получим ошибку #ССЫЛКА! (#REF!) . Если аргументом Таблица указан диапазон $B$1:$C$4 и необходимо вернуть данные из столбца С, то правильно будет указать номер столбца 2. Т.к. аргумент Таблица ( $B$1:$C$4 ) содержит только два столбца - В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА! (#REF!) , т.к. третьего столбца в указанном диапазоне просто нет.

Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО(в обеих таблицах ФИО относятся к разным отделам). Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и отделы и даты, не меняя вручную аргумент Номер_столбца: =ВПР( $A2 ; Лист1!$A$1:$C$4 ;СТОЛБЕЦ();0) . Такой подход сработает, если в обеих таблицах одинаковый порядок столбцов.

Как избежать ошибки #Н/Д(#N/A) в ВПР?

Еще частая проблема - многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это можно обойти при помощи специальных функций. Для пользователей Excel 2003 и старше: =ЕСЛИ(ЕНД(ВПР( $A2 ;Лист1! $A$1:$C$4 ;3;0));"";ВПР( $A2 ;Лист1! $A$1:$C$4 ;3;0)) =IF(ISNA(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)),"",VLOOKUP($A2,Лист1!$A$1:$C$4,3,0)) Теперь если ВПР не найдет совпадения, то ячейка будет пустой. А пользователям версий Excel 2007 и выше будет удобнее использовать функцию ЕСЛИОШИБКА (IFERROR) : =ЕСЛИОШИБКА(ВПР( $A2 ;Лист1! $A$1:$C$4 ;3;0);"") =IFERROR(VLOOKUP($A2,Лист1!$A$1:$C$4,3,0);"") Подробнее про различие между использованием ЕСЛИ(ЕНД и ЕСЛИОШИБКА я разбирал в статье: Как в ячейке с формулой вместо ошибки показать 0 Но я бы не рекомендовал использовать ЕСЛИОШИБКА (IFERROR) , не убедившись, что ошибки появляются только для реально отсутствующих значений. Иногда ВПР может вернуть #Н/Д и в других ситуациях:

искомое значение состоит более чем из 255 символов(решение этой проблемы приведено ниже в этой статье: Работа с критериями длиннее 255 символов)
искомое значение является числом с большим кол-вом знаков после запятой. Excel не может правильно воспринимать такие числа и в итоге ВПР может вернуть ошибку. Правильным решением здесь будет округлить искомое значение хотя бы до 4-х или 5-ти знаков после запятой(конечно, если это допустимо): =ВПР(ОКРУГЛ( $A2 ;5);Лист1! $A$1:$C$4 ;3;0) =VLOOKUP(ROUND($A2,2),Лист1!$A$1:$C$4,3,0)
искомое значение содержит специальные или непечатаемые символы. В этом случае придется либо избавиться от непечатаемых символов в искомом аргументе: =ВПР(ПЕЧСИМВ( $A2 );Лист1! $A$1:$C$4 ;3;0) =VLOOKUP(CLEAN($A2),Лист1!$A$1:$C$4,3,0) либо добавить перед всеми специальными символами(такими как звездочка или вопр.знак) знак тильды(

), чтобы сделать эти знаки просто знаками, а не знаками специального значения(так же работа со специальными(служебными) символами описывалась в статье: Как заменить/удалить/найти звездочку). Добавить символ перед знаком той же тильды можно при помощи функции ПОДСТАВИТЬ (SUBSTITUTE) : =ВПР(ПОДСТАВИТЬ( $A2 ;"

");Лист1! $A$1:$C$4 ;3;0) =VLOOKUP(SUBSTITUTE(A2,"

"),Лист1!$A$1:$C$4,3,0) Если необходимо добавить тильду сразу перед несколькими знаками, то делает это обычно так(на примере подстановки одновременно для тильды и звездочки): =ВПР(ПОДСТАВИТЬ(ПОДСТАВИТЬ( $A2 ;"

*");Лист1! $A$1:$C$4 ;3;0) =VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2,"

Решение при помощи ПОИСКПОЗ

Общий принцип работы ПОИСКПОЗ (MATCH) очень похож на ВПР - функция ищет заданное значение в массиве (в столбце или строке) и возвращает его позицию(порядковый номер в заданном массиве). Т.е. ищет Искомое_значение в аргументе Просматриваемый_массив и в качестве результата выдает номер позиции найденного значения в Просматриваемом_массиве . Именно номер позиции, а не само значение. Если бы мы хотели применить её для таблицы выше, то она была бы такой: =ПОИСКПОЗ( $A2 ; Лист1!$A$1:$A$4 ;0) =MATCH($A2,Лист1!$A$1:$A$4,0)

Искомое_значение( $A2 ) - непосредственно значение или ссылка на ячейку с искомым значением. Если опираться на пример выше - то это ФИО. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки * и ? и ровно в таком же исполнении.
Просматриваемый_массив( Лист1!$A$1:$A$4 ) - указывается ссылка на столбец, в котором необходимо найти искомое значение. В отличии от той же ВПР, где указывается целая таблица, это должен быть именно один столбец, в котором мы собираемся искать Искомое_значение . Если попытаться указать более одного столбца, то функция вернет ошибку. Справедливости ради надо отметить, что можно указать либо столбец, либо строку
Тип_сопоставления(0) - то же самое, что и Интервальный_просмотр в ВПР. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего.

С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС (INDEX) (которая возвращает из указанного диапазона значение на пересечении заданных строки и столбца) - то это то, что нам нужно и даже больше. =ИНДЕКС(Лист1! $A$1:$C$4 ;ПОИСКПОЗ( $A2 ;Лист1! $A$1:$A$4 ;0);2) Такая формула результатом вернет то же, что и ВПР.

Аргументы функции ИНДЕКС Массив(Лист1! $A$2:$C$4 ) . В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не обязательно или он всегда будет равен 1(столбец-то всего один). К слову - данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.

Далее идут Номер_строки и Номер_столбца . Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива , которое находится в указанной строке( Номер_строки ) Массива и указанном столбце( Номер_столбца ), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если не преследуются иные цели. Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же: Для всех версий Excel(включая 2003 и раньше): =ЕСЛИ(ЕНД(ПОИСКПОЗ( $A2 ;Лист1! $A$1:$A$4 ;0));"";ИНДЕКС(Лист1! $A$1:$C$4 ;ПОИСКПОЗ( $A2 ;Лист1! $A$2:$A$4 ;0);2)) Для версий 2007 и выше: =ЕСЛИОШИБКА(ИНДЕКС(Лист1! $A$1:$C$4 ;ПОИСКПОЗ( $A2 ;Лист1! $A$1:$A$4 ;0);2);"")

Работа с критериями длиннее 255 символов

Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов. Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий: =ВПР(ПСТР( $A2 ;1;255);ПСТР( Лист1!$A$1:$C$4 ;1;255);3;0) но это формула массива. Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются - формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.

Поэтому лучше использовать такую хитрую формулу: =ИНДЕКС( Лист1!$A$1:$C$4 ;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА; Лист1!$A$1:$A$4 = $A2 ;0));2) Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь. Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.

Ну и все же я рекомендовал бы Вам прочитать подробнее про данные функции в справке.

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

📎📎📎📎📎📎📎📎📎📎