Функция vlookup в excel примеры: Краткий справочник: функция ВПР — Служба поддержки Майкрософт

Как использовать функцию ВПР — devsap.ru

МЅ Excel: как использовать функцию ВПР

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

Функция ВПР является на самом деле очень легко использовать как только вы поймете, как это работает!

Синтаксис

Синтаксис функции ВПР в Microsoft Excel-это:

ВПР( значение, Таблица, номер_индекса, [approximate_match] )
Параметры или Аргументы

значение
Значение для поиска в первом столбце таблицы.
Таблица
Два или более столбцов данных, отсортированных по возрастанию.
номер_индекса
Номер столбца в таблице, из которой соответствующее значение должно быть возвращено. Первый столбец 1.
истина или ложь
Необязательно. Введите значение ЛОЖЬ (0), чтобы найти Точное совпадение. Введите ИСТИНА (1), чтобы найти приблизительное совпадение. Если этот параметр опущен, ИСТИНА  по умолчанию.

Примечание

• Если вы укажите значение ЛОЖЬ для параметра approximate_match и точное соответствие не найдено, то функция ВПР возвращает значение #Н/А.
• Если вы укажите значение ИСТИНА для параметра approximate_match и точное соответствие не найдено, то следующее меньшее значение возвращается.
• Если номер_индекса меньше 1, функция ВПР возвращает значение ошибки #знач!.
• Если номер_индекса больше, чем количество столбцов в таблице, то функция ВПР возвращает ошибку #ссылка!.
• См. также функция ГПР выполнять горизонтальный поиск.

Пример (как функция)

ВПР как функция листа в Excel.

Nаблицы Excel примеры ВПР:

=ВПР(10251, А1:В6, 2, ложь)
Результат: «значение возвращает груши» в 2-м столбце

=ВПР(10251, А1:С6, 3, ложь)
Результат: возвращает значение $18.60 в 3-й колонке

=ВПР(10251, А1:Д6, 4, ложные)
Результат: 9 ‘возвращает значение в 4-м столбце

=ВПР(10248, А1:В6, 2, ложь)
Результат: возвращает #н/д ‘ошибки #н/д (Точное совпадение)

=ВПР(10248, А1:В6, 2, истина)
Результат: «яблоки» ‘возвращает приблизительное совпадение

Теперь, давайте посмотрим на пример =ВПР(10251, А1:В6, 2, false), которое возвращает значение «груши» и присмотреться, почему.

Первый Параметр

Первый параметр функции ВПР искомое значение в таблице данных.

В этом примере первый параметр-10251. Это значение функция ВПР будет искать в первом столбце таблицы данных. Потому что это числовое значение, вы можете просто ввести номер. Но, если искомое значение текст, необходимо положить его в двойные кавычки, например:

=ВПР(«10251», А1:В6, 2, ложь)
Второй Параметр

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

В данном примере второй параметр А1:В6, которые дает нам два столбца данных для использования в вертикальном поиска — А1:А6 и В1:В6. Первый столбец в диапазоне (А1:А6) используется для поиска значения порядка 10251. Во втором столбце в диапазоне (В1:В6) содержится значение, возвращаемое значение продукта.

Третий Параметр

Третий параметр-номер позиции в таблице, где возвращают данные можно найти. Значение 1 указывает, что первый столбец в таблице. Второй столбец 2, и так далее.

В этом примере третий параметр-2. Это означает, что второй столбец в таблице, где мы найдете значение возвращать. Поскольку диапазон таблицы имеет значение А1:В6, возвращаемое значение будет во второй колонке где-то в диапазоне В1:В6.

Четвертый Параметр

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

В данном примере четвертый параметр имеет значение false. Параметр false означает, что функция ВПР ищет точное соответствие для значения 10251. Параметр True означает, что «закрыть» матч будет возвращена. С ВПР может найти значение 10251 в диапазоне А1:А6, она возвращает соответствующее значение из В1:В6, которые это груши.

Точное соответствие и приблизительное совпадение

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

Давайте подстановки значение, которое не существует в наши данные демонстрируют важность этого параметра!

Точное Совпадение

Используйте false, чтобы найти Точное совпадение:

=ВПР(10248, А1:В6, 2, ложь)
Результат: #Н/Д

Если нет точного совпадения, #н/д возвращается.

Приблизительное Совпадение

Используйте значение True, чтобы найти приблизительное совпадение:

=ВПР(10248, А1:В6, 2, истина)
Результат: «Яблоки»

Если совпадение не найдено, то возвращает ближайшее меньшее значение которой в данном случае является «яблоки».

ВПР из другого листа

Вы можете использовать функцию ВПР для поиска значения в случае, когда Таблица находится на другом листе. Давайте изменим наш пример выше, и предположим, что Таблица находится в другой лист под названием Лист2 в диапазоне А1:В6.

Мы можем переписать наш оригинальный пример, в котором мы найти значение 10251 следующим образом:

=ВПР(10251, Лист2!А1:В6, 2, ложь)

Перед таблицы с имя листа и восклицательный знак, мы можем обновить нашу vlookup для обращения к таблице на другом листе.

ВПР из другого листа с пробелами в имени листа

Давайте добавим сюда еще одно осложнение, что произойдет, если имя листа содержит пробелы, то вам потребуется дальнейшее изменение формулы.

Предположим, что Таблица на листе под названием «лист-тест» в диапазоне А1:В6, теперь нам нужно завернуть имя листа в одинарные кавычки, как показано ниже:

=ВПР(10251, ‘лист испытаний’!А1:В6, 2, ложь)

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

ВПР из другой книги

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

=ВПР(10251, и ‘C:[data.xlsx]Лист1’!$А$1:$В$6, 2, ЛОЖЬ)

Это будет искать значение 10251 в C:data.xlxs файл в 1 лист, где данные таблицы находится в диапазоне $A$1:$В$6.

Зачем использовать абсолютные ссылки?

Сейчас для нас важно, чтобы покрыть еще одна ошибка, которую часто совершают. Когда люди используют функции ВПР, они часто используют относительные ссылки на таблицы, как мы делали в наших примерах выше. Это вернет правильный ответ, но и что происходит при копировании формулы в другую ячейку? Диапазон Таблица будет корректироваться в Excel и изменения относительно того, где вы вставить новую формулу. Давай объясняй дальше…

Так что если вы имели следующую формулу в ячейку G1:

=ВПР(10251, А1:В6, 2, ложь)

И тогда вы скопировать эту формулу из ячейки G1 в ячейку Н2, он внесет изменения в формулу vlookup для этого:

=ВПР(10251, В2:С7, 2, ложь)

Так как ваша Таблица находится в диапазоне A1:B6 и не В2:С7, ваша формула будет возвращать неверные результаты в ячейке h3. Чтобы убедиться, что ваш выбор не меняется, попробуйте, ссылающиеся на таблицы с использованием абсолютной ссылки следующим образом:

=ВПР(10251, $А$1:$B В 6$, 2, ЛОЖЬ)

Теперь, если скопировать эту формулу в другую ячейку, диапазон таблицы будет оставаться $в$1:$В$6.

Как справиться с #Н/ошибки

Далее, давайте посмотрим на то, как обрабатывать случаи, когда функция ВПР не найдет совпадения и возвращает #н/д ошибка. В большинстве случаев, Вы не хотите видеть #Н/Д А дисплей более удобный результат.

Например, если у вас следующие формулы:

=ВПР(10248, $А$1:$B В 6$, 2, ЛОЖЬ)

Вместо отображения ошибки #н/д Если вам не удается найти соответствие, вы можете вернуть значение «не найдено». Для этого, можно изменить формулу ВПР следующим образом:

=Если(ЕОШ(ВПР(10248, $в$1:$B в 6$, 2, ложь)), «не нашли», ВПР(10248, $в$1:$В$6, 2, ложь))

Эта новая формула будет использована функция ИСНА, чтобы проверить, если функция ВПР возвращает значение #н/д ошибка. Если функция ВПР возвращает значение #н/д, тогда формула будет выход «не найдено». В противном случае он будет выполнять функцию ВПР как и раньше.

Это отличный способ, чтобы украсить вашу таблицу, так что Вы не увидите традиционных ошибок в Excel.

10 Примеры ВПР для начинающих и опытных пользователей

В этом учебном пособии рассматриваются:

Функция ВПР – Введение

Функция ВПР – это эталон.

Вы что-то знаете в Excel, если умеете пользоваться функцией ВПР.

Если вы этого не сделаете, вам лучше не указывать Excel в качестве одной из ваших сильных сторон в резюме.

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

Теперь, когда мы знаем, насколько важна эта функция Excel, имеет смысл полностью ее освоить, чтобы иметь возможность с гордостью сказать: «Я знаю кое-что в Excel».

Это будет большое руководство по ВПР (по моим меркам).

Я расскажу все, что нужно знать об этом, а затем покажу вам полезные и практические примеры ВПР.

Так что пристегнитесь.

Время взлета.

Когда использовать функцию ВПР в Excel?

Функция ВПР лучше всего подходит для ситуаций, когда вы ищете совпадающую точку данных в столбце, и когда совпадающая точка данных найдена, вы идете вправо в этой строке и выбираете значение из ячейки, которая является заданной. количество столбцов справа.

Давайте рассмотрим простой пример, чтобы понять, когда использовать Vlookup в Excel.

Помните, когда список результатов экзамена был расклеен на доске объявлений, и все сходили с ума, находя свои имена и свои баллы (по крайней мере, это случалось, когда я учился в школе).

Вот как это работает:

• Вы подходите к доске объявлений и начинаете искать свое имя или регистрационный номер (проводя пальцем сверху вниз по списку).
• Как только вы заметите свое имя, вы переместите взгляд вправо от имени/регистрационного номера, чтобы увидеть свои баллы.

И это именно то, что делает для вас функция Excel VLOOKUP (не стесняйтесь использовать этот пример в своем следующем интервью).

Функция ВПР ищет указанное значение в столбце (в приведенном выше примере это было ваше имя) и, найдя указанное совпадение, возвращает значение в той же строке (полученные вами отметки).

Синтаксис

= ВПР(значение_искателя, массив_таблицы, номер_индекса_столбца, [диапазон_искателя])

Входные аргументы

• искомое_значение – это искомое значение, которое вы пытаетесь найти в крайнем левом столбце таблицы . Это может быть значение, ссылка на ячейку или текстовая строка. В примере с оценочным листом это будет ваше имя.
• table_array — это массив таблиц, в котором вы ищете значение. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с оценочным листом это будет вся таблица, содержащая баллы для всех по каждому предмету
• col_index — это номер индекса столбца, из которого вы хотите получить соответствующее значение. В примере с оценочным листом, если вам нужны баллы по математике (это первый столбец в таблице, содержащей баллы), вы должны искать в столбце 1. Если вам нужны баллы по физике, вы должны искать в столбце 2.
• [range_lookup] — здесь вы указываете, хотите ли вы точное совпадение или приблизительное совпадение. Если этот параметр опущен, по умолчанию используется значение TRUE — приблизительное совпадение 9.0068 (см. дополнительные примечания ниже).

Дополнительные примечания (скучно, но важно знать)

• Совпадение может быть точным (FALSE или 0 в range_lookup) или приблизительным (TRUE или 1).
• При приблизительном поиске убедитесь, что список отсортирован по возрастанию (сверху вниз), иначе результат может быть неточным.
• Когда диапазон_просмотра имеет значение ИСТИНА (приблизительный поиск) и данные отсортированы в порядке возрастания:
  • Если функция ВПР не может найти значение, она возвращает наибольшее значение, которое меньше искомого_значения.
  • Возвращает ошибку #Н/Д, если искомое_значение меньше наименьшего значения.
  • Если искомое_значение является текстом, можно использовать подстановочные знаки (см. пример ниже).

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

10 примеров ВПР Excel (базовый и расширенный)

Вот 10 полезных примеров использования ВПР Excel, которые покажут вам, как использовать его в повседневной работе.

Пример 1. Определение балла Брэда по математике

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

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

Из вышеприведенных данных мне нужно узнать, сколько Брэд набрал баллов по математике.

Вот формула ВПР, которая вернет результат Брэда по математике:

 =ВПР("Брэд",$A$3:$E$10,2,0) 

Вышеприведенная формула имеет четыре аргумента:

• «Брэд: — это искомое значение.
• $A$3:$E$10 — это диапазон ячеек, в которых мы ищем. Помните, что Excel ищет значение поиска в крайнем левом столбце. В этом примере имя Brad будет искаться в A3:A10 (крайний левый столбец указанного массива).
• 2 — Как только функция обнаружит имя Брэда, она перейдет ко второму столбцу массива и вернет значение в той же строке, что и имя Брэда. Значение 2 здесь указывает на то, что мы ищем счет из второго столбца указанного массива.
• 0 — указывает функции ВПР искать только точные совпадения.

Вот как работает формула ВПР в приведенном выше примере.

Сначала он ищет значение Brad в крайнем левом столбце. Он идет сверху вниз и находит значение в ячейке A6.

Как только он находит значение, он идет вправо во втором столбце и выбирает значение из него.

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

Например, чтобы найти оценки Марии по химии, используйте следующую формулу ВПР:

 =ВПР("Мария",$A$3:$E$10,4,0) 

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

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

Например, если у вас есть ячейка с именем учащегося и вы получаете оценку по математике, результат будет автоматически обновляться при изменении имени учащегося (как показано ниже):

Если ввести значение поиска, которое не найдено в крайнем левом столбце, будет возвращена ошибка #Н/Д.

Пример 2. Двусторонний поиск

В приведенном выше примере 1 мы жестко закодировали значение столбца. Следовательно, формула всегда будет возвращать оценку по математике, поскольку мы использовали 2 в качестве номера индекса столбца.

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

Это пример двусторонней функции ВПР.

Чтобы сделать эту формулу двустороннего поиска, вам также нужно сделать столбец динамическим. Поэтому, когда пользователь меняет тему, формула автоматически выбирает правильный столбец (2 в случае математики, 3 в случае физики и т. д.).

Для этого нужно использовать функцию ПОИСКПОЗ в качестве аргумента столбца.

Вот формула ВПР, которая сделает это:

 =ВПР(G4,$A$3:$E$10,MATCH(h4,$A$2:$E$2,0),0) 

В приведенной выше формуле в качестве номера столбца используется ПОИСКПОЗ(h4,$A$2:$E$2,0). Функция ПОИСКПОЗ принимает имя субъекта в качестве значения поиска (в h4) и возвращает его позицию в A2:E2. Следовательно, если вы используете Math, он вернет 2, так как Math находится в ячейке B2 (которая является второй ячейкой в ​​указанном диапазоне массива).

Пример 3. Использование выпадающих списков в качестве значений поиска

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

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

В зависимости от выбора формула автоматически обновит результат.

Примерно так, как показано ниже:

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

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

В данном случае используется та же формула ВПР, что и в примере 2.

 =ВПР(G4,$A$3:$E$10,MATCH(h4,$A$2:$E$2) ,0),0) 

Значения поиска преобразованы в раскрывающиеся списки.

Вот шаги для создания раскрывающегося списка:

• Выберите ячейку, в которой вы хотите разместить раскрывающийся список. В этом примере в G4 нам нужны имена учеников.
• Перейдите в раздел «Данные» -> «Инструменты данных» -> «Проверка данных».
• В диалоговом окне «Проверка данных» на вкладке настроек выберите «Список» в раскрывающемся списке «Разрешить».
• В источнике выберите $A$3:$A$10
• Нажмите OK.

Теперь у вас будет раскрывающийся список в ячейке G4. Точно так же вы можете создать один в h4 для предметов.

Пример 4. Трехсторонний поиск

Что такое трехсторонний поиск?

В примере 2 мы использовали одну справочную таблицу с оценками учащихся по разным предметам. Это пример двустороннего поиска, поскольку мы используем две переменные для получения оценки (имя учащегося и имя субъекта).

Теперь предположим, что за год учащийся сдает три разных уровня экзаменов: модульный тест, промежуточный экзамен и итоговый экзамен (это то, что было у меня, когда я был студентом).

Трехсторонний поиск — это возможность получить оценки учащегося по определенному предмету на заданном уровне экзамена.

Примерно так, как показано ниже:

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

Вот формула, используемая в ячейке h5:

 =ВПР(G4,ВЫБОР(ЕСЛИ(h3="Единичный тест",1,ЕСЛИ(h3="Промежуточный",2,3)),$A$3:$ E$7,$A$11:$E$15,$A$19:$E$23),MATCH(h4,$A$2:$E$2,0),0)  

Эта формула использует функцию ВЫБОР, чтобы убедиться, что правильный таблица упоминается. Давайте проанализируем часть ВЫБОР формулы:

ВЫБОР(ЕСЛИ(h3=»Единичный тест»,1,ЕСЛИ(h3=»Промежуточный»,2,3)),$A$3:$E$7,$A$11: $E$15,$A$19:$E$23)

Первый аргумент формулы – ЕСЛИ(h3=»Единичный тест»,1,ЕСЛИ(h3=»Промежуточный тест»,2,3)), который проверяет ячейку h3 и посмотрите, о каком уровне экзамена идет речь. Если это модульное тестирование, он возвращает $A$3:$E$7, который содержит баллы для модульного тестирования. Если это промежуточный срок, он возвращает $A$11:$E$15, в противном случае он возвращает $A$19.:$Е$23.

Это делает массив таблицы ВПР динамическим и, следовательно, делает его трехсторонним поиском.

Пример 5. Получение последнего значения из списка

Вы можете создать формулу ВПР для получения последнего числового значения из списка.

Наибольшее положительное число, которое можно использовать в Excel,  9.99999999999999E+307 . Это также означает, что самый большой номер поиска в номере ВПР тоже одинаков.

Я не думаю, что вам когда-либо понадобятся какие-либо вычисления с таким большим числом. И это именно то, что мы можем использовать для получения последнего числа в списке.

Предположим, у вас есть набор данных (в A1:A14) , как показано ниже, и вы хотите получить последний номер в списке.

Вот формула, которую вы можете использовать:

 =VLOOKUP(9. 99999999999999E+307,$A$1:$A$14,  TRUE)  

Обратите внимание, что в приведенной выше формуле используется приблизительное соответствие VLOOKUP8 ( 90 в конце формулы вместо FALSE или 0). Также обратите внимание, что для работы этой формулы ВПР список не нужно сортировать.

Вот как примерно работает функция ВПР. Он сканирует крайний левый столбец сверху вниз.

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

В приведенном выше примере работает третий сценарий.

С 9.99999999999999E+307 — это наибольшее число, которое можно использовать в Excel. Когда оно используется в качестве значения поиска, оно возвращает последнее число из списка.

Таким же образом вы также можете использовать его для возврата последнего текстового элемента из списка. Вот формула, которая может это сделать:

 =VLOOKUP("zzz",$A$1:$A$8,1,  TRUE  ) 

Далее следует та же логика. Excel просматривает все имена, и, поскольку zzz считается большим, чем любое имя/текст, начинающийся с алфавита до zzz, он вернет последний элемент из списка.

Пример 6. Частичный поиск с использованием подстановочных знаков и ВПР

Подстановочные знаки Excel могут быть очень полезны во многих ситуациях.

Это волшебное зелье, которое придает вашим формулам сверхспособности.

Частичный поиск необходим, когда нужно найти значение в списке, а точное совпадение отсутствует.

Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите найти компанию ABC в списке, но в списке есть ABC Ltd вместо ABC.

Вы не можете использовать ABC в качестве значения поиска, так как нет точного совпадения в столбце A. Приблизительное совпадение также приводит к ошибочным результатам и требует сортировки списка в порядке возрастания.

Однако вы можете использовать подстановочный знак в функции ВПР, чтобы получить совпадение.

Введите следующую формулу в ячейку D2 и перетащите ее в другие ячейки:

 =ВПР("*"&C2&"*",$A$2:$A$8,1,FALSE) 

Как это работает? формула работает?

В приведенной выше формуле вместо того, чтобы использовать искомое значение как есть, оно окружено с обеих сторон подстановочным знаком звездочкой (*) – «*»&C2&»*»

Звездочка является подстановочным знаком в Excel и может представлять любое количество символов.

Использование звездочки с обеих сторон значения поиска указывает Excel, что необходимо искать любой текст, содержащий слово в C2. Он может иметь любое количество символов до или после текста в C2.

Например, в ячейке C2 есть ABC, поэтому функция ВПР просматривает имена в A2:A8 и ищет ABC. Он находит совпадение в ячейке A2, так как содержит ABC в ABC Ltd. Не имеет значения, есть ли символы слева или справа от ABC. Пока в текстовой строке нет буквы ABC, она будет считаться совпадением.

Примечание. Функция ВПР всегда возвращает первое совпавшее значение и прекращает поиск. Поэтому, если у вас есть ABC Ltd., и ABC Corporation в списке, он вернет первый и проигнорирует остальные.

Пример 7. Функция ВПР возвращает ошибку, несмотря на совпадение в искомом значении

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

Например, в приведенном ниже случае есть совпадение (Мэтт), но функция ВПР по-прежнему возвращает ошибку.

Теперь, хотя мы видим, что совпадение есть, невооруженным глазом мы не можем увидеть, что могут быть начальные или конечные пробелы. Если у вас есть эти дополнительные пробелы до, после или между значениями поиска, это НЕ ТОЧНОЕ совпадение.

Это часто бывает, когда вы импортируете данные из базы данных или получаете их от кого-то другого. Эти начальные/конечные пробелы имеют тенденцию проникать внутрь.

Решением здесь является функция TRIM. Он удаляет все начальные или конечные пробелы или лишние пробелы между словами.

Вот формула, которая даст правильный результат.

 =VLOOKUP("Matt",TRIM($A$2:$A$9),1,0) 

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

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

Пример 8. Выполнение поиска с учетом регистра

По умолчанию значение поиска в функции ВПР не чувствительно к регистру. Например, если искомое значение — МАТТ, матовый или матовый, для функции ВПР все то же самое. Он вернет первое совпадающее значение независимо от регистра.

Но если вы хотите выполнить поиск с учетом регистра, вам нужно использовать функцию EXACT вместе с функцией VLOOKUP.

Вот пример:

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

Теперь функция ВПР не может обрабатывать значения поиска с учетом регистра. В приведенном выше примере он всегда будет возвращать 38, что является оценкой для Мэтта в A2.

Чтобы сделать его чувствительным к регистру, нам нужно использовать вспомогательный столбец (как показано ниже):

Чтобы получить значения во вспомогательном столбце, используйте функцию =СТРОКА(). Он просто получит номер строки в ячейке.

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

 =VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(СТРОКА($A$2:$A$9))),$B$2:$C$9,2,0) 

Теперь давайте разбить и понять, что это делает:

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

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

Пример 9. Использование ВПР с несколькими критериями

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

Но часто возникает необходимость использовать ВПР в Excel с несколькими критериями.

Предположим, у вас есть данные с именем учащегося, типом экзамена и оценкой по математике (как показано ниже):

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

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

Это можно сделать с помощью вспомогательного столбца. Первый шаг — вставить вспомогательный столбец слева от оценок.

Теперь, чтобы создать уникальный квалификатор для каждого экземпляра имени, используйте следующую формулу в ячейке C2: =A2&”|”&B2

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

Теперь, несмотря на то, что имена повторяются, повторений при объединении имени с уровнем проверки нет.

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

Вот формула, которая даст вам результат в G3:I8.

 =VLOOKUP($F3&"|"&G$2,$C$2:$D$19,2,0) 

Здесь мы объединили имя студента и уровень экзамена, чтобы получить значение поиска, и мы используем этот поиск value и проверяет его во вспомогательном столбце, чтобы получить соответствующую запись.

Примечание. В приведенном выше примере мы использовали | в качестве разделителя при объединении текста во вспомогательном столбце. В некоторых исключительно редких (но возможных) условиях у вас могут быть два разных критерия, но в конечном итоге они дают один и тот же результат при объединении. Вот пример:

Обратите внимание, что, хотя A2 и A3 разные, а B2 и B3 разные, комбинации в конечном итоге будут одинаковыми. Но если использовать разделитель, то даже комбинация будет другой (D2 и D3).

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

Пример 10. Обработка ошибок при использовании функции ВПР

Функция ВПР Excel возвращает ошибку, если не может найти указанное значение поиска. Возможно, вы не хотите, чтобы уродливое значение ошибки нарушало эстетику ваших данных, если функция ВПР не может найти значение.

Вы можете легко удалить значения ошибок с полным текстом любого значения, например, «Недоступно» или «Не найдено».

Например, в приведенном ниже примере при попытке найти в списке счет Брэда возвращается ошибка, поскольку имя Брэда отсутствует в списке.

Чтобы удалить эту ошибку и заменить ее чем-то осмысленным, заключите функцию ВПР в функцию ЕСЛИОШИБКА.

Вот формула:

 =ЕСЛИОШИБКА(ВПР(D2,$A$2:$B$7,2,0),"Не найдено") 

Функция ЕСЛИОШИБКА проверяет, является ли значение, возвращаемое первым аргументом (в данном случае функцией ВПР), ошибкой или нет. Если это не ошибка, возвращает значение функцией ВПР, иначе возвращает Not Found.

Функция ЕСЛИОШИБКА доступна, начиная с Excel 2007. Если вы используете более ранние версии, используйте следующую функцию:

 =ЕСЛИ(ЕОШИБКА(ВПР(D2,$A$2:$B$7,2,0)),"Не найдено",ВПР(D2,$A $2:$B$7,2,0)) 

См. также: Как обрабатывать ошибки ВПР в Excel.

Вот и все в этом руководстве по ВПР.

Я попытался охватить основные примеры использования функции ВПР в Excel. Если вы хотите, чтобы в этот список было добавлено больше примеров, дайте мне знать в разделе комментариев.

Примечание. Я старался изо всех сил вычитывать этот учебник, но если вы обнаружите какие-либо ошибки или орфографические ошибки, сообщите мне об этом 🙂

Использование  Функция ВПР в Excel – Видео

Связанные функции Excel:

• Функция ГПР в Excel.
• Функция XLOOKUP в Excel
• Функция ИНДЕКС в Excel.
• Функция Excel НЕПРЯМАЯ.
• Excel Функция ПОИСКПОЗ.
• Функция СМЕЩЕНИЯ Excel.

Вам также могут понравиться следующие учебники по Excel:

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

Как использовать функцию ВПР в Excel (простое руководство)

Точное совпадение | Примерное совпадение | ВПР выглядит правильно | Первый матч | Частичное совпадение | Vlookup нечувствителен к регистру | Множественные критерии | #Н/Д ошибка | Несколько таблиц поиска | Индекс и соответствие | Магия стола | Xlookup

Функция ВПР — одна из самых популярных функций в Excel . Эта страница содержит много простых примеров VLOOKUP.

Точное совпадение

Большую часть времени вы ищете точное совпадение при использовании функции ВПР в Excel. Давайте посмотрим на аргументы функции ВПР.

1. Приведенная ниже функция ВПР ищет значение 53 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент).

2. Значение 4 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из четвертого столбца красной таблицы.

Примечание: логическое значение ЛОЖЬ (четвертый аргумент) указывает функции ВПР вернуть точное совпадение. Если функция ВПР не может найти значение 53 в первом столбце, она вернет ошибку #Н/Д.

3. Вот еще пример. Вместо зарплаты приведенная ниже функция ВПР возвращает фамилию (третьему аргументу присвоено значение 3) идентификатора 79.

Приблизительное совпадение

Давайте рассмотрим пример функции ВПР в режиме приблизительного совпадения ( четвертый аргумент установлен в значение TRUE).

1. Приведенная ниже функция ВПР ищет значение 85 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент). Есть только одна проблема. В первом столбце нет значения 85.

2. К счастью, логическое значение ИСТИНА (четвертый аргумент) указывает функции ВПР вернуть приблизительное совпадение. Если функция ВПР не может найти значение 85 в первом столбце, она вернет наибольшее значение меньше 85. В этом примере это будет значение 80.

3. Значение 2 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из второго столбца красной таблицы.

Примечание: всегда сортируйте крайний левый столбец красной таблицы в порядке возрастания, если вы используете функцию ВПР в режиме приблизительного совпадения (четвертый аргумент имеет значение ИСТИНА).

ВПР ищет правильно

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

1. Например, функция ВПР ниже ищет имя и возвращает фамилию.

2. Если вы измените порядковый номер столбца (третий аргумент) на 3, функция ВПР ищет имя и возвращает зарплату.

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

Первое совпадение

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

Объяснение: функция ВПР возвращает зарплату Мии Кларк, а не Мии Рид.

Частичное совпадение

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

Объяснение: звездочка (*) соответствует нулю или более символов. Функция ВПР сопоставляет первый экземпляр, начинающийся с «Джесс». Используйте «*» и G2 для совпадения «заканчивается на». Используйте «*»&G2&»*» для соответствия «содержит».

ВПР нечувствителен к регистру

Функция ВПР в Excel выполняет поиск без учета регистра. Например, приведенная ниже функция ВПР ищет MIA (ячейка G2) в крайнем левом столбце таблицы.

Объяснение: функция ВПР нечувствительна к регистру, поэтому она ищет МИА, МИА, МИА или МИА и т. д. В результате функция ВПР возвращает зарплату Миа Кларк (первый экземпляр). Используйте ИНДЕКС, ПОИСКПОЗ и ИСТОЧНИК в Excel для выполнения поиска с учетом регистра.

Несколько критериев

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

Примечание: приведенная выше формула массива ищет зарплату Джеймса Кларка, а не Джеймса Смита и не Джеймса Андерсона.

Ошибка #Н/Д

Если функция ВПР не может найти совпадение, она возвращает ошибку #Н/Д.

1. Например, приведенная ниже функция ВПР не может найти значение 28 в крайнем левом столбце.

2. Если хотите, вы можете использовать функцию IFNA, чтобы заменить ошибку #N/A дружественным сообщением.

Примечание: функция IFNA была введена в Excel 2013. Если вы используете Excel 2010 или Excel 2007, объедините IF и ISNA или используйте IFERROR.

Несколько таблиц поиска

При использовании функции ВПР в Excel у вас может быть несколько таблиц поиска. Вы можете использовать функцию ЕСЛИ, чтобы проверить, выполняется ли условие, и вернуть одну таблицу поиска, если ИСТИНА, и другую таблицу поиска, если ЛОЖЬ.

1. Создайте два именованных диапазона: Table1 и Table2.

2. Выберите ячейку E4 и введите функцию ВПР, показанную ниже.

Объяснение: бонус зависит от рынка (Великобритания или США) и суммы продаж. Второй аргумент функции ВПР делает свое дело. В Великобритании функция ВПР использует Таблицу 1, в США функция ВПР использует Таблицу 2. Установите для четвертого аргумента функции ВПР значение ИСТИНА, чтобы возвратить приблизительное совпадение.

3. Нажмите Enter.

4. Выберите ячейку E4, щелкните в правом нижнем углу ячейки E4 и перетащите ее вниз к ячейке E10.

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

Индекс и сопоставление

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

Table Magic

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

Примечание. Посетите нашу страницу об объединении таблиц, чтобы узнать, как создать эту функцию ВПР и вывести свою игру Excel на новый уровень.