Формула Vlookup с условием If

В учебнике показано, как комбинировать VПРОСМОТР и ЕСЛИ работают вместе для v-lookup с условием if в Excel. Вы также узнаете, как использовать формулы IF ISNA VLOOKUP для замены ошибок #N/A собственным текстом, нулевой или пустой ячейкой.

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

Vlookup с оператором If: возвращает True/False, Yes/No и т. д.

Один из наиболее распространенных сценариев, когда вы объединяете If и Vlookup вместе, — это сравнение значения, возвращаемого Vlookup, с образцом значения и возвращением Да нет или же Верно / Ложно в результате.

В большинстве случаев хорошо работает следующая общая формула:

ЕСЛИ(ВПР(…) = ценностьИСТИНА, ЛОЖЬ)

В переводе на простой английский формула предписывает Excel вернуть Истинный если Vlookup истинно (т.е. равно указанному значению). Если Vlookup ложно (не равно указанному значению), формула возвращает ЛОЖЬ.

Ниже вы найдете несколько реальных применений этой формулы IF Vlookup.

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

Допустим, у вас есть список товаров в столбце A и количество в столбце B. Вы создаете информационную панель для своих пользователей и вам нужна формула, которая будет проверять количество товара в E1 и сообщать пользователю, есть ли товар на складе. или распроданы.

Вы получаете количество с помощью обычного ВПР с формулой точного соответствия, например:

=ВПР(E1,$A$2:$B$10,2,ЛОЖЬ)

Затем напишите оператор IF, который сравнивает результат Vlookup с нулем и возвращает «Нет», если он равен 0, и «Да» в противном случае:

=ЕСЛИ(ВПР(E1,$A$2:$B$10,2,ЛОЖЬ)=0,”Нет”,”Да”)
Если формула Vlookup возвращает Да или Нет на основе результата vlookup

Вместо Да нетвы можете вернуться ИСТИНА/ЛОЖЬ или же В наличии/Распродано или любые другие два варианта. Например:

=ЕСЛИ(ВПР(E1,$A$2:$B$10,2)=0,”Распродано”,”На складе”)

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

=ЕСЛИ(ВПР(E1,$A$2:$B$10,2)=”пример текста”,ИСТИНА,ЛОЖЬ)

Пример 2. Сравните результат ВПР с другой ячейкой

Другим типичным примером ВПР с условием Если в Excel является сравнение вывода ВПР со значением в другой ячейке. Например, мы можем проверить, больше или равно ли оно числу в ячейке G2:

=ЕСЛИ(ВПР(E1,$A$2:$B$10,2)>=G2,”Да!”,”Нет”)

А вот наша формула If с Vlookup в действии:
Если формула с ВПР для сравнения результата ВПР с другой ячейкой

Аналогичным образом вы можете использовать любой другой логический оператор вместе со ссылкой на ячейку в формуле Excel If Vlookup.

Пример 3. Значения ВПР в более коротком списке

Чтобы сравнить каждую ячейку в целевом столбце с другим списком и вернуть Истинный или же Да если совпадение найдено, ЛОЖЬ или же Нет в противном случае используйте эту общую формулу IF ISNA VLOOKUP:

ЕСЛИ(ИСНА(ВПР(…)),”Нет”,”Да”)

Если Vlookup приводит к ошибке #N/A, формула возвращает «Нет», что означает, что искомое значение не найдено в списке поиска. Если совпадение найдено, возвращается «Да». Например:

=ЕСЛИ(ISNA(ВПР(A2,$D$2:$D$4,1,ЛОЖЬ)),”Нет”,”Да”)
Значения Vlookup в более коротком списке и возвращают Да или Нет.

Если ваша бизнес-логика требует противоположных результатов, просто поменяйте местами «Да» и «Нет», чтобы изменить логику формулы:

=ЕСЛИ(ISNA(ВПР(A2,$D$2:$D$4,1,ЛОЖЬ)),”Да”,”Нет”)
Формула IF ISNA VLOOKUP для поиска значений в более коротком списке и возврата Да или Нет.

Формула Excel If Vlookup для выполнения различных вычислений

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

Продолжая наш пример, рассчитаем комиссию конкретного продавца (F1) в зависимости от их эффективности: 20% комиссии для тех, кто заработал 200 долларов и больше, 10% для всех остальных.

Для этого вы проверяете, больше или равно ли значение, возвращаемое ВПР, 200, и если да, то умножаете его на 20%, иначе на 10%:

=ЕСЛИ(ВПР(F1,$A$2:$C$10,3,ЛОЖЬ)>=200, ВПР(F1,$A$2:$C$10,3,ЛОЖЬ)*20%, ВПР(F1,$A$2 :$C$10,3,ЛОЖЬ)*10%)

Где A2:A10 — имена продавцов, а C2:C10 — продажи.
Формула Excel If Vlookup для выполнения различных вычислений

IF ISNA VLOOKUP для скрытия ошибок #N/A

Если функция ВПР не может найти указанное значение, она выдает ошибку #Н/Д. Чтобы отловить эту ошибку и заменить ее своим текстом, вставьте формулу ВПР в логическую проверку функции ЕСЛИ, например так:

ЕСЛИ(ИСНА(ВПР(…)), «Не найдено», ВПР(…))

Естественно, вместо «Не найдено» можно набрать любой текст.

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

С именами в A2:A10 и суммами C2:C10 задачу можно выполнить с помощью следующей формулы If Vlookup:

=ЕСЛИ(ИСНА(ВПР(F1,$A$2:$C$10,3,ЛОЖЬ)), “Не найдено”, ВПР(F1,$A$2:$C$10,3,ЛОЖЬ))

Если имя найдено, возвращается соответствующая сумма продаж:
ЕСЛИ формула ISNA VLOOKUP извлекает совпадающее значение

Если искомое значение не найдено, Не найден появляется сообщение вместо ошибки #Н/Д:
Если искомое значение не найдено, функция IF ISNA VLOOKUP возвращает пользовательский текст вместо ошибки N/A.

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

Логика формулы очень проста: вы используете функцию ISNA для проверки Vlookup на наличие ошибок #N/A. В случае ошибки ISNA возвращает TRUE, в противном случае FALSE. Приведенные выше значения поступают на логическую проверку функции ЕСЛИ, которая выполняет одно из следующих действий:

  • Если логическая проверка ИСТИНА (ошибка #Н/Д), отображается ваше сообщение.
  • Если логический тест FALSE (значение поиска найдено), Vlookup обычно возвращает совпадение.

IFNA VLOOKUP в новых версиях Excel

Начиная с Excel 2013, вы можете использовать функцию IFNA вместо IF ISNA для перехвата и обработки ошибок #N/A:

ЕСЛИНА(ВПР(…), “Не найден“)

В нашем примере формула примет следующий вид:

=ЕСЛИНА(ВПР(F1,$A$2:$C$10,3, ЛОЖЬ), “Не найдено”)

Кончик. Если вы хотите перехватывать всевозможные ошибки, а не только #Н/Д, используйте функцию ВПР в сочетании с функцией ЕСЛИОШИБКА. Более подробную информацию можно найти здесь: IFERROR VLOOKUP в Excel.

Excel Vlookup: если не найден, верните 0

При работе с числовыми значениями вы можете захотеть вернуть ноль, если искомое значение не найдено. Для этого используйте формулу IF ISNA VLOOKUP, описанную выше, с небольшой модификацией: вместо текстового сообщения введите 0 в значение_если_истина аргумент функции ЕСЛИ:

ЕСЛИ(ИСНА(ВПР(…)), 0, ВПР(…))

В нашем образце таблицы формула будет выглядеть следующим образом:

=ЕСЛИ(ИСНА(ВПР(F2,$A$2:$C$10,3,ЛОЖЬ)), 0, ВПР(F2,$A$2:$C$10,3,ЛОЖЬ))
Если формула Vlookup: если не найдено, вернуть 0

В последних версиях Excel 2016 и 2013 вы можете снова использовать комбинацию IFNA Vlookup:

=ЕСЛИНА(ВПР(I2,$A$2:$C$10,3, ЛОЖЬ), 0)

Excel Vlookup: если не найдено, верните пустую ячейку

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

ЕСЛИ(ИСНА(ВПР(…)), “”, ВПР(…))

Ниже приведены несколько примеров полных формул:

Для всех версий Excel:

=ЕСЛИ(ИСНА(ВПР(F2,$A$2:$C$10,3,ЛОЖЬ)), “”, ВПР(F2,$A$2:$C$10,3,ЛОЖЬ))

Для Excel 2016 и Excel 2013:

=ЕСЛИНА(ВПР(F2,$A$2:$C$10,3, ЛОЖЬ), “”)
Если формула Vlookup: если не найдено, вернуть пустое значение (пустая строка)

If with Index Match – левый vlookup с условием If

Опытные пользователи Excel знают, что функция ВПР — не единственный способ вертикального поиска в Excel. Для этой цели также можно использовать комбинацию INDEX MATCH, которая еще более мощная и универсальная. Хорошей новостью является то, что Index Match может работать вместе с IF точно так же, как Vlookup.

Например, у вас есть номера заказов в столбце A и имена продавцов в столбце B. Вы ищете формулу для получения номера заказа для конкретного продавца.

В этом случае нельзя использовать Vlookup, потому что он не может выполнять поиск справа налево. Сопоставление индекса будет работать без сучка и задоринки, пока искомое значение находится в столбце поиска. В противном случае появится ошибка #N/A. Чтобы заменить стандартную запись ошибки своим собственным текстом, вложите Index Match внутрь IF ISNA:

=ЕСЛИ(ИСНА(ИНДЕКС(A2:A10, ПОИСКПОЗ(F1, $B$2:$B$10, 0))), “Не найдено”, ИНДЕКС(A2:A10, ПОИСКПОЗ(F1, $B$2:$B$10 , 0)))

В Excel 2016 и 2016 вы можете использовать IFNA вместо IF ISNA, чтобы сделать формулу более компактной:

=ЕСЛИНА(ИНДЕКС(A2:A10, ПОИСКПОЗ(F1, $B$2:$B$10, 0)), “Не найдено”)
Использование If с Index Match для поиска слева без ошибок N/A

Аналогичным образом вы можете использовать Index Match в других формулах If.

Вот как вы используете оператор Vlookup и оператор IF вместе в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Excel IF Vlookup – примеры формул (файл .xlsx)

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *