=ВПР(значение;если(логическое выражение ;таблица 1;таблица 2);номер столбца;НЕ точное совпадение)

Связанные формулы

ВПР с числами и текстом
ВПР с двумя или более критериями
ВПР с индексом вычисляемого столбца
ВПР без ошибки #н/д

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

В примере, показанном формула в ячейке E8:

=ВПР(D8;ЕСЛИ(C8<2;TABLE1;TABLE2);2;истина)

В этой формуле используется число лет, id продавца и процент премии за объём продаж  в компании, чтобы определить, какую премию начислять используем  Таблицы 1 и 2 с тарифами.

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

Если функция в этой формуле, используется как «табличный_массив» аргумент ВПР, выполняет логическую проверку на значение в столбце C «Год». Если С8 меньше 2, то таблица 1 возвращается как значение если «ИСТИНА». Если С8 больше 2, таблица 2 возвращается как значение если «ЛОЖЬ».

Иными словами, если лет меньше, чем 2, используется таблица 1 и, если нет, то используется таблица 2.

Альтернативный синтаксис

Если таблицы подстановки требуют различных правил обработки, затем вы можете обернуть двумя функциями ВПР внутри функции «ЕСЛИ» следующим образом:

=Если(логическое выражение ,ВПР (значение;таблица 1;колонка;матч),ВПР (значение;таблица 2;коль;матч))

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

=ВПР(значение&»*»;таблица;колонка;ЛОЖЬ)

Получить информацию с помощью ВПР

Если вы хотите извлечь информацию из таблицы на основе частичного совпадения, это можно сделать с помощью ВПР в точном режиме поиска то-есть ЛОЖЬ.

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

=ВПР($H$5&»*»,таб2,2,ложь)

В этой формуле,  именованный диапазон, который ссылается на B5:Е8. Без именованных диапазонов, формула может быть записана следующим образом:

=ВПР($H$5&»*»,$В$5:$Е$8,2,ЛОЖЬ)

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

ВПР поддерживает подстановочные знаки, но только в режиме «Точное совпадение». Чтобы задать точное соответствие, убедитесь, что вы поставить 4й параметр как ЛОЖЬ или 0.

В этом случае, мы поставляем подстановки значения как $H$5&»*», поэтому если мы наберем в строке «10» в именованный диапазон ($H$5), мы даем ВПР «10*» в качестве подстановки значения.

Это вызовет в ВПР  поиск по первому столбцу B, который начинается с «10».

Шаблоны-это удобно, поскольку вам не придется вводить полное имя, но учтите, что вы должны быть осторожны, дубликатов или дублей. Например, в таблице есть «10» и в других строках и вам выдаст первый результат поиска.

Примечание: важно установить точное соответствие с использованием ложь или 0.

Файл EXCEL Частичное-совпадение-с-ВПР-devsap

Функции-Excel-ВПР-DEVSAP

Формула примеры

Получить первое текстовое значение в списке

Текст карты к номерам

Динамические таблицы подстановки с косвенным

ВПР с числами и текстом

ВПР с двумя или более критериями

ВПР без ошибки #н/д

Автономные ВПР

Построить гиперссылку с ВПР

Номера групп с ВПР

Получить информацию о сотрудниках с ВПР

Цель

Поиск значения в таблице путем сопоставления на первый столбец

Возвращаемое значение

Соответствующим значением из таблицы.

Синтаксис

=ВПР (значение; Таблица; номер столбца; правило сравнения)

Аргументы

значение — искомое значение в первом столбце таблицы.

Таблица — Таблица, из которой, чтобы получить значение.

Номер столбца — столбца в таблице, из которой нужно извлечь значение.

Правило сравнения — [необязательно] правда = приблизительное совпадение (по умолчанию). Ложь = Точное совпадение.

Примечания

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

Использовать ВПР, когда значения подстановки расположены в первом столбце таблицы с информацией, организованной по вертикали. Использование ГПР, когда значения подстановки расположены в первой строке таблицы, и каждая «запись» отображается в новой колонке.

Правило сравнения управляет значение должен точно соответствовать или нет. Значение по умолчанию-ИСТИН = разрешить не Точное совпадение.

Набор Правило сравнения ЛОЖЬ, чтобы требовать точного соответствия и значение ИСТИНА, чтобы разрешить не-Точное совпадение.

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

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

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

Если Правило сравнения ЛОЖЬ (требуется Точное совпадение), значения в первом столбце таблицы не должны быть отсортированы.

Файл Функции-Excel-ВПР-devsap

=ВПР(критерий поиска;таблица;колонка поиска;ИСТИНА)

Связанные формулы

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

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

В показанном примере, формула в E5 является:

=ВПР(D5;$G$5:$H$8;2;ИСТИНА)

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

Эта формула использует значение в ячейке D5 и значения подстановки, именованный диапазон «$G$5:$H$8» для таблицы подстановки, 2, чтобы указать «2-й столбец», и последний аргумент указывает приблизительное совпадение.

Примечание: последний аргумент является необязательным и по умолчанию имеет значение ИСТИНА, но я хотел, чтобы явно установить соответствующий режим.

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

ФАЙЛ EXCEL Поиск-по-возрастным-ВПР-и-формирование-групп-devsap

=ВПР(критерий;таблицы;столбец;0)

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

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

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

=ВПР(Номер;товар;2;0) // оригинал
=ВПР(Номер&»»;товар;2,0) // пересмотренный

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

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

=ВПР(ТЕКСТ(Номер;»@»);товар;2;0)

Если у вас есть цифры такие-же как текст

Если Вы не уверены, что все будет верно используйте ВПР с iferror :

=Функции iferror(ВПР(Номер;Товар;2;0),ВПР(Номер&»»;товар;2;0))

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

Файл EXCEL ВПР-с-числами-и-текстом-исправление-ошибки-devsap