Впр ошибка неверный формат данных

Функция ВПР не работает – способы устранения ошибок Н/Д, ИМЯ и ЗНАЧ

​Смотрите также​serlas77​alex1248​​ в ячейку С2.​​FL_Sales​Lookup table 2​ПОИСКПОЗ(«Mar»;$A$1:$I$1;0)​Введите эту формулу массива​ выглядеть гораздо проще:​ другие. Я благодарю​ выясним, какое животное​ на «man»:​​(таблица), переключитесь на​​до​

​Так как функция​.​Если такая ситуация со​​Этот урок объясняет, как​​: Z,​:​Данные, представленные таким образом,​,​, а​В переводе на человеческий​ в несколько смежных​=VLOOKUP(B2&» «&C2,Orders,4,FALSE)​​ Вас за то,​​ может передвигаться со​=VLOOKUP(«*man»,$A$2:$C$11,1,FALSE)​ другую рабочую книгу​A15​ЕСЛИОШИБКА​Если же аргумент​

​ многими числами, выделите​ быстро справиться с​если бы вы​​yderpleks​​ можно сопоставлять. Находить​​TX_Sales​​3​​ язык, данная формула​​ ячеек, например, в​=ВПР(B2&» «&C2;Orders;4;ЛОЖЬ)​​ что читаете этот​​ скоростью​=ВПР(«*man»;$A$2:$C$11;1;ЛОЖЬ)​ и выделите в​, потому что A​появилась в Excel​col_index_num​ их и щелкните​​ ситуацией, когда функция​​ внимательно читали моё​, а что вы​ численную и процентную​и так далее.​– это столбец​

• ​ означает:​
• ​ ячейки​Чтобы формула работала, значения​
• ​ учебник, и надеюсь​
• ​50​~​
• ​ ней нужный диапазон​ – это первый​ 2007, при работе​

Исправляем ошибку #Н/Д функции ВПР в Excel

​(номер_столбца) больше количества​​ по выделенной области​​ВПР​​ сообщение и правила​​ вообще пытаетесь сделать​​ разницу.​​ Как видите, во​ C, содержащий цены.​Ищем символы «Mar» –​F4:F8​ в крайнем левом​ встретить Вас снова​

1. Искомое значение написано с опечаткой

​миль в час.​Находим имя, начинающееся​ поиска.​ столбец диапазона A2:B15,​ в более ранних​ столбцов в заданном​ правой кнопкой мыши.​(VLOOKUP) не хочет​ форума — поняли​ этой формулой? Совершенно​

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

​До сих пор мы​ всех именах присутствует​На рисунке ниже виден​ аргумент​​, как показано на​​ столбце просматриваемой таблицы​ на следующей неделе!​ Я верю, что​ на «ad» и​На снимке экрана, показанном​​ заданного в аргументе​​ версиях Вам придётся​

• ​ массиве,​ В появившемся контекстном​ работать в Excel​
• ​ бы, без проблем.​ не видно логики​

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

​ предлагали для анализа​ «_Sales».​​ результат, возвращаемый созданной​​lookup_value​ рисунке ниже. Количество​ должны быть объединены​Урок подготовлен для Вас​ вот такая формула​​ заканчивающееся на «son»:​​ ниже, видно формулу,​table_array​ использовать комбинацию​ВПР​​ меню выберите​​ 2013, 2010, 2007​

4. Столбец поиска не является крайним левым

​Специально для вас​ ее построения в​ только одно условие​​Функция​​ нами формулой:​(искомое_значение);​ ячеек должно быть​ точно так же,​ командой сайта office-guru.ru​ не вызовет у​=VLOOKUP(«ad*son»,$A$2:$C$11,1,FALSE)​ в которой для​(таблица):​ЕСЛИ​сообщит об ошибке​Format Cells​​ и 2003, а​​ уточняю:​

​ вашем файле. Да​​ – наименование материала.​ДВССЫЛ​В начале разъясним, что​Ищем в ячейках от​ равным или большим,​ как и в​​Источник: https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/​​ Вас затруднений:​​=ВПР(«ad*son»;$A$2:$C$11;1;ЛОЖЬ)​​ поиска задан диапазон​=VLOOKUP(40,A2:B15,2)​​(IF) и​​#REF!​

5. Числа форматированы как текст

​(Формат ячеек) >​​ также, как выявить​​1. По правилам​​ и результат, как​​ На практике же​соединяет значение в​ мы подразумеваем под​ A1 до I1​ чем максимально возможное​

​ критерии поиска. На​Перевел: Антон Андронов​=VLOOKUP(50,$A$2:$B$15,2,FALSE)​~​ в рабочей книге​=ВПР(40;A2:B15;2)​ЕОШИБКА​(#ССЫЛ!).​

​ вкладка​ и исправить распространённые​ форума, нельзя заливать​ написано в «учебнике»:​

​ нередко требуется сравнить​ столбце D и​ выражением «Динамическая подстановка​​ – аргумент​​ число повторений искомого​ рисунке выше мы​Автор: Антон Андронов​=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)​Находим первое имя​PriceList.xlsx​col_index_num​(ISERROR) вот так:​Простейший случай – ошибка​

​Number​​ ошибки и преодолеть​ файлы размером более​yderpleks​ несколько диапазонов с​​ текстовую строку «_Sales»,​​ данных из разных​lookup_array​

​ значения. Не забудьте​ объединили значения и​Во второй части нашего​Обратите внимание, что наш​ в списке, состоящее​на листе​(номер_столбца) – номер​​=IF(ISERROR(VLOOKUP формула),»Ваше сообщение при​​#NAME?​(Число) > формат​​ ограничения​​ 100 кб. Размер​​: Эммм… А поподробнее​​ данными и выбрать​​ тем самым сообщая​​ таблиц», чтобы убедиться​

6. В начале или в конце стоит пробел

​(просматриваемый_массив);​ нажать​​ поставили между ними​​ учебника по функции​​ диапазон поиска (столбец​​ из 5 символов:​Prices​ столбца в заданном​ ошибке»,VLOOKUP формула)​(#ИМЯ?) – появится,​Number​ВПР​

​ моего файла в​ нельзя??​ значение по 2,​ВПР​

​ правильно ли мы​Возвращаем точное совпадение –​Ctrl+Shift+Enter​ пробел, точно так​ВПР​​ A) содержит два​​=VLOOKUP(«?????»,$A$2:$C$11,1,FALSE)​​.​​ диапазоне, из которого​

​=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при​
​ если Вы случайно​

​(Числовой) и нажмите​.​ нормальном, экселевском формате​

​RAN​ 3-м и т.д.​в какой таблице​ понимает друг друга.​​ аргумент​​, чтобы правильно ввести​​ же необходимо сделать​​(VLOOKUP) в Excel​​ значения​​=ВПР(«?????»;$A$2:$C$11;1;ЛОЖЬ)​Функция​ будет возвращено значение,​​ ошибке»;ВПР формула)​​ напишите с ошибкой​​ОК​​В нескольких предыдущих статьях​​ — почти 400​​: Нельзя.​

​ критериям.​
​ искать. Если в​

​Бывают ситуации, когда есть​match_type​ формулу массива.​​ в первом аргументе​​ мы разберём несколько​​50​​Чтобы функция​ВПР​

Ошибка #ЗНАЧ! в формулах с ВПР

​ находящееся в найденной​Например, формула​ имя функции.​​.​​ мы изучили различные​ кб.​Pelena​Таблица для примера:​ ячейке D3 находится​​ несколько листов с​​(тип_сопоставления).​Если Вам интересно понять,​​ функции (B2&» «&C2).​​ примеров, которые помогут​

1. Искомое значение длиннее 255 символов

​– в ячейках​​ВПР​​будет работать даже,​ строке.Крайний левый столбец​ЕСЛИ+ЕОШИБКА+ВПР​Решение очевидно – проверьте​Это наименее очевидная причина​ грани функции​2. Файл в​​:​​Предположим, нам нужно найти,​

​ значение «FL», формула​​ данными одного формата,​​Использовав​​ как она работает,​Запомните!​ Вам направить всю​A5​

​с символами подстановки​
​ когда Вы закроете​

2. Не указан полный путь к рабочей книге для поиска

​ в заданном диапазоне​, аналогична формуле​ правописание!​ ошибки​ВПР​ csv залит с​yderpleks​ по какой цене​ выполнит поиск в​ и необходимо извлечь​0​ давайте немного погрузимся​Функция​ мощь​и​ работала правильно, в​ рабочую книгу, в​ – это​ЕСЛИОШИБКА+ВПР​

​Помимо достаточно сложного синтаксиса,​​#Н/Д​​в Excel. Если​ примерами формул, которые​

​, Вы не ответили​
​ привезли гофрированный картон​

​ таблице​ нужную информацию с​

​в третьем аргументе,​
​ в детали формулы:​

​ВПР​ВПР​​A6​​ качестве четвёртого аргумента​​ которой производится поиск,​​1​​, показанной выше:​​ВПР​​в работе функции​​ Вы читали их​ я использовал, т.к.​​ на вопрос​​ от ОАО «Восток».​

​FL_Sales​ определенного листа в​ Вы говорите функции​​IF($F$2=B2:B16,ROW(C2:C16)-1,»»)​​ограничена 255 символами,​на решение наиболее​. Формула возвращает значение​​ всегда нужно использовать​​ а в строке​, второй столбец –​=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),»»,VLOOKUP($F$2,$B$2:$C$10,2,FALSE))​имеет больше ограничений,​

​ВПР​ внимательно, то сейчас​​ вес исходника xlsx​​RAN​ Нужно задать два​, если «CA» –​ зависимости от значения,​ПОИСКПОЗ​ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»)​

3. Аргумент Номер_столбца меньше 1

​ она не может​ амбициозных задач Excel.​ из ячейки​​FALSE​​ формул появится полный​ это​=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));»»;ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))​ чем любая другая​, поскольку зрительно трудно​ должны быть экспертом​ больше разрешенного, на​​: А по моему​​ условия для поиска​

​ в таблице​ которое введено в​​искать первое значение,​​$F$2=B2:B16​​ искать значение, состоящее​​ Примеры подразумевают, что​​B5​​(ЛОЖЬ). Если диапазон​ путь к файлу​​2​​На сегодня всё. Надеюсь,​

​ функция Excel. Из-за​​ увидеть эти лишние​​ в этой области.​ этом форуме.​ все понятно.​​ по наименованию материала​​CA_Sales​​ заданную ячейку. Думаю,​​ в точности совпадающее​

Ошибка #ИМЯ? в ВПР

​– сравниваем значение​​ из более чем​​ Вы уже имеете​. Почему? Потому что​ поиска содержит более​ рабочей книги, как​

​, третий столбец –​ этот короткий учебник​

ВПР не работает (ограничения, оговорки и решения)

​ этих ограничений, простые​​ пробелы, особенно при​​ Однако не без​3. Я залил​Человек хочет найти​ и по поставщику.​и так далее.​ проще это объяснить​​ с искомым значением.​​ в ячейке F2​ 255 символов. Имейте​ базовые знания о​ при поиске точного​ одного значения, подходящего​​ показано ниже:​​ это​

1. ВПР не чувствительна к регистру

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

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

2. ВПР возвращает первое найденное значение

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

3. В таблицу был добавлен или удалён столбец

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

​ неожиданным результатам. Ниже​​Решение 1: Лишние пробелы​ сложных функций. Она​​ в экселе, т.к.​​ там, где светло​​ левый столбец (важно!),​​будет следующий:​​ одинаковыми товарами и​​ВПР​СТРОКА(C2:C16)-1​Соглашусь, добавление вспомогательного столбца​ с первой части​ искомым.​А теперь давайте разберём​ апострофы:​=VLOOKUP(40,A2:B15,2)​ формулы работать правильно.​ Вы найдёте решения​ в основной таблице​

4. Ссылки на ячейки исказились при копировании формулы

​ имеет кучу ограничений​ они просили предоставить​

​ (48).​​ объединив «Поставщиков» и​Если данные расположены в​ в одинаковом формате.​​.​​возвращает номер соответствующей​ – не самое​​ этого учебника, в​​Когда Вы используете функцию​​ чуть более сложный​​=VLOOKUP(40,'[Numbers.xlsx]Sheet2′!A2:B15,2)​=ВПР(40;A2:B15;2)​Урок подготовлен для Вас​ для нескольких распространённых​​ (там, где функция​​ и особенностей, которые​

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

​ такую возможность. Неужели​PS но нарушителям​ «Материалы».​​ разных книгах Excel,​​ Требуется найти показатели​​Вот так Вы можете​​ строки (значение​​ изящное и не​​ которой объясняются синтаксис​ВПР​ пример, как осуществить​=ВПР(40;'[Numbers.xlsx]Sheet2′!A2:B15;2)​​Формула ищет значение​​ командой сайта office-guru.ru​​ сценариев, когда​​ ВПР)​ становятся источником многих​ трудно догадаться?!​ правил конкретнее не​​Таким же образом объединяем​​ то необходимо добавить​ продаж для определенного​

ВПР: работа с функцией ЕСЛИОШИБКА

​ создать формулу для​​-1​​ всегда приемлемое решение.​ и основное применение​для поиска приблизительного​

​ поиск с помощью​
​Если Вы планируете использовать​

​40​Источник: https://www.ablebits.com/office-addins-blog/2014/08/27/excel-vlookup-not-working/​ВПР​Если лишние пробелы оказались​ проблем и ошибок.​Может стоит научиться​ отвечаю. Мяу.​ искомые критерии запроса:​

​ имя книги перед​ региона:​ поиска по двум​позволяет не включать​

​ Вы можете сделать​
​ВПР​

​ совпадения, т.е. когда​ функции​ один диапазон поиска​в диапазоне​​Перевел: Антон Андронов​​ошибается.​ в основной таблице,​

​В этой статье Вы​ внимательно читать, прежде​
​hitman1316​Теперь ставим курсор в​

ВПР: работа с функцией ЕОШИБКА

​ именованным диапазоном, например:​​Если у Вас всего​​ критериям в Excel,​ строку заголовков). Если​ то же самое​. Что ж, давайте​ аргумент​​ВПР​​ в нескольких функциях​​A2:A15​​Автор: Антон Андронов​

​Функция​ Вы можете обеспечить​
​ найдёте простые объяснения​ чем проявлять агрессию​

​: Добрый вечер!​​ нужном месте и​​=VLOOKUP($D$2,INDIRECT($D3&»Workbook1!_Sales»),2,FALSE)​​ два таких отчета,​​ что также известно,​

​ совпадений нет, функция​
​ без вспомогательного столбца,​

​ приступим.​range_lookup​по значению в​ВПР​и возвращает соответствующее​​Сегодня мы начинаем серию​​ВПР​ правильную работу формул,​

​ ошибок​ и направо-налево бесполезные​
​Есть таблица, Номер​
​ задаем аргументы для​

​=ВПР($D$2;ДВССЫЛ($D3&»Workbook1!_Sales»);2;ЛОЖЬ)​

office-guru.ru

Функция ВПР в Excel – руководство для начинающих: синтаксис и примеры

​ то можно использовать​ как двумерный поиск​IF​ но в таком​​Поиск в Excel по​​(интервальный_просмотр) равен​ какой-то ячейке. Представьте,​, то можете создать​ значение из столбца​ статей, описывающих одну​

​не различает регистр​​ заключив аргумент​​#N/A​ советы раздавать?​ комнаты задается из​ функции: . Excel​Если функция​ до безобразия простую​ или поиск в​(ЕСЛИ) возвращает пустую​ случае потребуется гораздо​ нескольким критериям​TRUE​​ что в столбце​​ именованный диапазон и​

• ​ B (поскольку B​
• ​ из самых полезных​
  • ​ и принимает символы​lookup_value​(#Н/Д),​
  • ​JayBhagavan​ списка, тип определяется​ находит нужную цену.​
  • ​ДВССЫЛ​ формулу с функциями​ двух направлениях.​
  • ​ строку.​ более сложная формула​
• ​Извлекаем 2-е, 3-е и​(ИСТИНА) или пропущен,​
• ​ A находится список​ вводить его имя​

Функция ВПР в Excel – общее описание и синтаксис

​ – это второй​​ функций Excel –​​ нижнего и ВЕРХНЕГО​(искомое_значение) в функцию​#NAME?​: serlas77, просто приложите​ по формуле ВПР,​Рассмотрим формулу детально:​ссылается на другую​ВПР​​Функция​​Результатом функции​ с комбинацией функций​ т.д. значения, используя​ первое, что Вы​ лицензионных ключей, а​ в формулу в​

​ столбец в диапазоне​ВПР​​ регистра как одинаковые.​​TRIM​(#ИМЯ?) и​ файл в формате​ а вот стоимость​Что ищем.​ книгу, то эта​

​и​СУММПРОИЗВ​​IF​​INDEX​​ ВПР​​ должны сделать, –​​ в столбце B​​ качестве аргумента​ A2:B15).​​(VLOOKUP). Эта функция,​​ Поэтому, если в​​(СЖПРОБЕЛЫ):​​#VALUE!​ xlsx/xlsm/xlsb. Спасибо.​ по формуле ВПР​Где ищем.​​ книга должна быть​​ЕСЛИ​​(SUMPRODUCT) возвращает сумму​​(ЕСЛИ) окажется вот​

​(ИНДЕКС) и​​Извлекаем все повторения искомого​​ выполнить сортировку диапазона​ список имён, владеющих​table_array​Если значение аргумента​ в то же​ таблице есть несколько​

Синтаксис функции ВПР

​=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)​​(#ЗНАЧ!), появляющихся при​​Z​ не хочет работать,​

​Какие данные берем.​
​ открытой. Если же​

​(IF), чтобы выбрать​​ произведений выбранных массивов:​​ такой горизонтальный массив:​MATCH​ значения​ по первому столбцу​ лицензией. Кроме этого,​(таблица).​

• ​col_index_num​​ время, одна из​ элементов, которые различаются​=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)​ работе с функцией​: OFF​ почему непонятно…​Допустим, какие-то данные у​ она закрыта, функция​ нужный отчет для​=SUMPRODUCT(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»),$A$2:$I$9)​{1,»»,3,»»,5,»»,»»,»»,»»,»»,»»,12,»»,»»,»»}​​(ПОИСКПОЗ).​​Двумерный поиск по известным​

  ​ в порядке возрастания.​
​ у Вас есть​

​Чтобы создать именованный диапазон,​(номер_столбца) меньше​ наиболее сложных и​ только регистром символов,​Решение 2: Лишние пробелы​​ВПР​​Однако, JayBhagavan, он​​Половину вычисляет, результат​​ нас сделаны в​

• ​ сообщит об ошибке​​ поиска:​=СУММПРОИЗВ(($A$2:$A$9=»Lemons»)*($A$1:$I$1=»Mar»);$A$2:$I$9)​ROW()-3​​Вы уже знаете, что​​ строке и столбцу​Это очень важно, поскольку​ часть (несколько символов)​ просто выделите ячейки​​1​​ наименее понятных.​ функция ВПР возвратит​ в таблице поиска​, а также приёмы​ весит под 400​ отображает, а в​ виде раскрывающегося списка.​#REF!​=VLOOKUP($D$2,IF($D3=»FL»,FL_Sales,CA_Sales),2,FALSE)​В следующей статье я​СТРОКА()-3​​ВПР​​Используем несколько ВПР в​​ функция​​ какого-то лицензионного ключа​​ и введите подходящее​​, то​В этом учебнике по​ первый попавшийся элемент,​ (в столбце поиска)​​ и способы борьбы​​ (правда, в РАРе​

  ​ остальных местах #Н/Д...​
​ В нашем примере​

• ​(#ССЫЛ!).​​=ВПР($D$2;ЕСЛИ($D3=»FL»;FL_Sales;CA_Sales);2;ЛОЖЬ)​ буду объяснять эти​Здесь функция​может возвратить только​ одной формуле​ВПР​ в ячейке C1,​ название в поле​​ВПР​​ВПР​ не взирая на​​Если лишние пробелы оказались​​ с ними. Мы​ — 90), а​​MacSieM​​ – «Материалы». Необходимо​Урок подготовлен для Вас​Где:​

  ​ функции во всех​
​ROW​

  ​ одно совпадающее значение,​​Динамическая подстановка данных из​​возвращает следующее наибольшее​​ и Вы хотите​​Имя​сообщит об ошибке​я постараюсь изложить​ регистр.​ в столбце поиска​ начнём с наиболее​

  

​ дать только 2​​:​​ настроить функцию так,​​ командой сайта office-guru.ru​​$D$2​​ деталях, так что​​(СТРОКА) действует как​​ точнее – первое​​ разных таблиц​ значение после заданного,​ найти имя владельца.​​, слева от строки​​#VALUE!​ основы максимально простым​​Решение:​​ – простыми путями​

• ​ частых случаев и​​ листа или 2​hitman1316​
  • ​ чтобы при выборе​Источник: https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/​​– это ячейка,​​ сейчас можете просто​
  • ​ дополнительный счётчик. Так​​ найденное. Но как​​Функция​ а затем поиск​

  ​Это можно сделать, используя​ формул.​(#ЗНАЧ!). А если​ языком, чтобы сделать​​Используйте другую функцию​​ ошибку​ наиболее очевидных причин,​ таблицы, или самому​, проблема была в​ наименования появлялась цена.​Перевел: Антон Андронов​

Примеры с функцией ВПР

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

Как, используя ВПР, выполнить поиск на другом листе Excel

​ процесс обучения для​ Excel, которая может​​#Н/Д​​ почему​ разобраться с циклическими/перекрестными​ сортировке комнат (в​Сначала сделаем раскрывающийся список:​Автор: Антон Андронов​ Обратите внимание, здесь​=INDEX($A$2:$I$9,MATCH(«Lemons»,$A$2:$A$9,0),MATCH(«Mar»,$A$1:$I$1,0))​

​ в ячейки F4:F9,​​ просматриваемом массиве это​​в Excel –​ пренебрежете правильной сортировкой,​=VLOOKUP(«*»&C1&»*»,$A$2:$B$12,2,FALSE)​ вот такую формулу​​ столбцов в диапазоне​​ неискушённых пользователей максимально​ выполнить вертикальный поиск​в формуле с​ВПР​ ссылками — это​ справочнике). Для ВПР​​Ставим курсор в ячейку​​Функция ВПР в Excel​ мы используем абсолютные​​=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ(«Lemons»;$A$2:$A$9;0);ПОИСКПОЗ(«Mar»;$A$1:$I$1;0))​​ мы вычитаем число​

​ значение повторяется несколько​
​ это действительно мощный​

​ дело закончится тем,​=ВПР(«*»&C1&»*»;$A$2:$B$12;2;FALSE)​ для поиска цены​table_array​ понятным. Кроме этого,​ (ПРОСМОТР, СУММПРОИЗВ, ИНДЕКС​​ВПР​​не работает, поэтому​ для ТС знаете​ нужно, чтобы данные​ Е8, где и​

​ позволяет данные из​ ссылки, чтобы избежать​Если Вы не в​3​ раз, и Вы​ инструмент для выполнения​​ что Вы получите​​Эта формула ищет значение​

​ товара​
​(таблица), функция вернет​

​ мы изучим несколько​ и ПОИСКПОЗ) в​не избежать. Вместо​ лучше изучать примеры​ ли… Да, и​ были отсортированы по​ будет этот список.​

​ одной таблицы переставить​​ изменения искомого значения​​ восторге от всех​из результата функции,​ хотите извлечь 2-е​ поиска определённого значения​ очень странные результаты​ из ячейки C1​Product 1​ ошибку​

Поиск в другой рабочей книге с помощью ВПР

​ примеров с формулами​​ сочетании с​​ВПР​ в том порядке,​ в гулдокс поздно​ возрастанию. Для того,​Заходим на вкладку «Данные».​ в соответствующие ячейки​

​ при копировании формулы​ этих сложных формул​​ чтобы получить значение​​ или 3-е из​​ в базе данных.​​ или сообщение об​​ в заданном диапазоне​​:​

​#REF!​
​ Excel, которые продемонстрируют​

​СОВПАД​Вы можете использовать​ в каком они​​ стучаться…​​ чтобы не было​ Меню «Проверка данных».​

1. ​ второй. Ее английское​ в другие ячейки.​ Excel, Вам может​1​ них? А что​ Однако, есть существенное​ ошибке​ и возвращает соответствующее​=VLOOKUP(«Product 1»,Products,2)​
2. ​(#ССЫЛКА!).​​ наиболее распространённые варианты​​, которая различает регистр.​ формулу массива с​​ приведены в статье.​​serlas77​ проблем (в Вашем​Выбираем тип данных –​ наименование – VLOOKUP.​$D3​

​ понравиться вот такой​в ячейке​ если все значения?​ ограничение – её​#N/A​​ значение из столбца​​=ВПР(«Product 1»;Products;2)​​range_lookup​​ использования функции​

​ Более подробно Вы​​ комбинацией функций​​Исправляем ошибку #Н/Д​: Z, спасибо за​ примере их не​ «Список». Источник –​Очень удобная и часто​– это ячейка​ наглядный и запоминающийся​F4​ Задачка кажется замысловатой,​

​ синтаксис позволяет искать​(#Н/Д).​ B. Обратите внимание,​Большинство имен диапазонов работают​(интервальный_просмотр) – определяет,​

​ВПР​
​ можете узнать из​

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

​ИНДЕКС​Исправляем ошибку #ЗНАЧ! в​ подсказку, прикладываю rar​​ будет, но на​​ диапазон с наименованиями​ используемая. Т.к. сопоставить​ с названием региона.​ способ:​(строка 4, вычитаем​​ но решение существует!​​ только одно значение.​

​Вот теперь можно использовать​ что в первом​ для всей рабочей​ что нужно искать:​​.​​ урока — 4​(INDEX),​

​ формулах с ВПР​gling​ будущее) используйте последний​ материалов.​​ вручную диапазоны с​​ Используем абсолютную ссылку​

​Выделите таблицу, откройте вкладку​
​ 3), чтобы получить​

​Предположим, в одном столбце​ Как же быть,​ одну из следующих​ аргументе мы используем​ книги Excel, поэтому​точное совпадение, аргумент должен​​Общее описание и синтаксис​​ способа сделать ВПР​ПОИСКПОЗ​Ошибка #ИМЯ? в ВПР​: Значение Ad Group​ аргумент в значении​Когда нажмем ОК –​ десятками тысяч наименований​ для столбца и​Formulas​2​ таблицы записаны имена​

​ если требуется выполнить​
​ формул:​

​ символ амперсанда (&)​ нет необходимости указывать​ быть равен​Примеры с функцией ВПР​ с учетом регистра​(MATCH) и​ВПР не работает (проблемы,​ 1, вы ищите​ ЛОЖЬ, чтобы поиск​ сформируется выпадающий список.​ проблематично.​ относительную ссылку для​(Формулы) и нажмите​в ячейке​

​ клиентов (Customer Name),​ поиск по нескольким​=VLOOKUP(69,$A$2:$B$15,2,TRUE)​​ до и после​​ имя листа для​​FALSE​​Как, используя ВПР, выполнить​ в Excel.​СЖПРОБЕЛЫ​ ограничения и решения)​ в столбце в​ был точным.​Теперь нужно сделать так,​Допустим, на склад предприятия​

​ строки, поскольку планируем​Create from Selection​

​F5​
​ а в другом​

​ условиям? Решение Вы​

​или​
​ ссылки на ячейку,​

​ аргумента​(ЛОЖЬ);​ поиск на другом​Как Вы уже знаете,​(TRIM):​ВПР – работа с​​ первом столбце таблицы​​update: кажется погорячился,​ чтобы при выборе​

Использование символов подстановки в формулах с ВПР

​ по производству тары​ копировать формулу в​​(Создать из выделенного).​​(строка 5, вычитаем​ – товары (Product),​

• ​ найдёте далее.​=VLOOKUP(69,$A$2:$B$15,2)​ чтобы связать текстовую​
• ​table_array​приблизительное совпадение, аргумент равен​

​ листе Excel​ВПР​​=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))​​ функциями ЕСЛИОШИБКА и​ AdwordsAds, а оно​

• ​ сказав, что сортировка​ определенного материала в​ и упаковки поступили​
• ​ другие ячейки того​Отметьте галочками​ 3) и так​ которые они купили.​​Предположим, у нас есть​​=ВПР(69;$A$2:$B$15;2;ИСТИНА)​ строку.​(таблица), даже если​​TRUE​​Поиск в другой рабочей​возвращает из заданного​
• ​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))​ ЕОШИБКА​ в таблице AdwordsAds,​ обязательна. Как правильно​ графе цена появлялась​ материалы в определенном​ же столбца.​Top row​ далее.​

Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

​ Попробуем найти 2-й,​ список заказов и​или​Как видно на рисунке​ формула и диапазон​(ИСТИНА) или вовсе​ книге с помощью​ столбца значение, соответствующее​Так как это формула​В формулах с​ находится в столбце​

​ заметили ниже, последний​
​ соответствующая цифра. Ставим​

​ количестве.​FL_Sal​(в строке выше)​SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​ 3-й и 4-й​ мы хотим найти​=ВПР(69;$A$2:$B$15;2)​ ниже, функция​ поиска находятся на​​ не указан.​​ ВПР​ первому найденному совпадению​ массива, не забудьте​ВПР​ В.​

​ параметр все же​
​ курсор в ячейку​

​Стоимость материалов – в​es​

​ и​​НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​ товары, купленные заданным​

​Количество товара​
​Как видите, я хочу​

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

​ с искомым. Однако,​
​ нажать​

​сообщение об ошибке​​RAN​ решает.​ Е9 (где должна​

​ прайс-листе. Это отдельная​
​и​

​Left column​​Функция​​ клиентом.​(Qty.), основываясь на​ выяснить, у какого​возвращает значение «Jeremy​​ Если же они​​ но очень важен.​ или таблицу в​ Вы можете заставить​Ctrl+Shift+Enter​#N/A​: И формулы рабочие?​gling​

Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР

​ будет появляться цена).​ таблица.​CA_Sales​(в столбце слева).​SMALL​​Простейший способ – добавить​​ двух критериях –​ из животных скорость​ Hill», поскольку его​ находятся в разных​ Далее в этом​ формулах с ВПР​ ее извлечь 2-е,​вместо привычного​(#Н/Д) – означает​JayBhagavan​:​Открываем «Мастер функций» и​Необходимо узнать стоимость материалов,​– названия таблиц​

​ Microsoft Excel назначит​(НАИМЕНЬШИЙ) возвращает​

​ вспомогательный столбец перед​
​Имя клиента​

​ ближе всего к​ лицензионный ключ содержит​ книгах, то перед​ учебнике по​Использование символов подстановки в​ 3-е, 4-е или​Enter​not available​: Как понял. Слова​hitman1316​ выбираем ВПР.​ поступивших на склад.​ (или именованных диапазонов),​

​ имена диапазонам из​n-ое​​ столбцом​​(Customer) и​69​ последовательность символов из​ именем диапазона нужно​ВПР​

​ формулах с ВПР​​ любое другое повторение​​, чтобы правильно ввести​(нет данных) –​ и Текст:​,​Первый аргумент – «Искомое​ Для этого нужно​

Точное или приближенное совпадение в функции ВПР

​ в которых содержаться​ значений в верхней​наименьшее значение в​Customer Name​​Название продукта​​милям в час.​​ ячейки C1.​​ указать название рабочей​я покажу Вам​Точное или приближенное совпадение​ значения, которое Вам​ формулу.​ появляется, когда Excel​=ВПР([@[Название группы]];AdwordsAds[[Ad Group]:[Headline]];2;0)​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(D3;цены;2;0)​ значение» — ячейка​​ подставит цену из​​ соответствующие отчеты о​​ строке и левом​​ массиве данных. В​

​и заполнить его​(Product). Дело усложняется​ И вот какой​Заметьте, что аргумент​ книги, к примеру,​

• ​ несколько примеров, объясняющих​​ в функции ВПР​​ нужно. Если нужно​​В большинстве случаев, Microsoft​​ не может найти​=ВПР([@[Название группы]];AdwordsAds[[Ad Group]:[Description​или Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(D3;цены;2;) будет​ с выпадающим списком.​ второй таблицы в​​ продажах. Вы, конечно​​ столбце Вашей таблицы.​ нашем случае, какую​ именами клиентов с​​ тем, что каждый​​ результат мне вернула​table_array​ вот так:​​ как правильно составлять​​ВПР в Excel –​ извлечь все повторяющиеся​ Excel сообщает об​ искомое значение. Это​ line 1]];4;0)&» «&ВПР([@[Название​​ работать​​ Таблица – диапазон​ первую. И посредством​​ же, можете использовать​​ Теперь Вы можете​ по счёту позицию​ номером повторения каждого​​ из покупателей заказывал​​ функция​

  ​(таблица) на скриншоте​
​=VLOOKUP("Product 1",PriceList.xlsx!Products,2)​

• ​ формулы для поиска​​ это нужно запомнить!​​ значения, Вам потребуется​​ ошибке​​ может произойти по​ группы]];AdwordsAds[[Ad Group]:[H+D1]];6;0)​Hugo​​ с названиями материалов​​ обычного умножения мы​ обычные названия листов​ осуществлять поиск, используя​ (от наименьшего) возвращать​ имени, например,​ несколько видов товаров,​ВПР​​ сверху содержит имя​​=ВПР(«Product 1»;PriceList.xlsx!Products;2)​

​ точного и приблизительного​​Итак, что же такое​​ комбинация из функций​​#VALUE!​​ нескольким причинам.​serlas77​: Последний параметр не​ и ценами. Столбец,​ найдем искомое.​ и ссылки на​ эти имена, напрямую,​ – определено функцией​​John Doe1​​ как это видно​:​

​ таблицы (Table7) вместо​Так формула выглядит гораздо​​ совпадения.​​ВПР​​ИНДЕКС​​(#ЗНАЧ!), когда значение,​Хорошая мысль проверить этот​: Всё не так.​​ желаете указать?​​ соответственно, 2. Функция​Алгоритм действий:​

Пример 1: Поиск точного совпадения при помощи ВПР

​ диапазоны ячеек, например​ без создания формул.​ROW​​,​​ из таблицы ниже:​​Как видите, формула возвратила​​ указания диапазона ячеек.​

​ понятнее, согласны? Кроме​Я надеюсь, функция​? Ну, во-первых, это​(INDEX),​ использованное в формуле,​ пункт в первую​​ Ищу значение «Заголовок»,​​hitman1316​ приобрела следующий вид:​Приведем первую таблицу в​‘FL Sheet’!$A$3:$B$10​В любой пустой ячейке​

​(СТРОКА) (смотри Часть​
​John Doe2​

​Обычная функция​ результат​ Так мы делали​ того, использование именованных​​ВПР​​ функция Excel. Что​​НАИМЕНЬШИЙ​​ не подходит по​​ очередь! Опечатки часто​​ которое в «AdwordsAds»​: спасибо большое)​​ .​​ нужный нам вид.​, но именованные диапазоны​ запишите​​ 2). Так, для​​и т.д. Фокус​ВПР​Антилопа​

Пример 2: Используем ВПР для поиска приблизительного совпадения

​ в предыдущем примере.​​ диапазонов – это​​стала для Вас​ она делает? Она​(SMALL) и​​ типу данных. Что​​ возникают, когда Вы​​ помечено, как «Headline».​​я думал он​Нажимаем ВВОД и наслаждаемся​ Добавим столбцы «Цена»​ гораздо удобнее.​=имя_строки имя_столбца​ ячейки​

​ с нумерацией сделаем​не будет работать​​(Antelope), скорость которой​​И, наконец, давайте рассмотрим​ хорошая альтернатива абсолютным​ чуть-чуть понятнее. Теперь​ ищет заданное Вами​СТРОКА​ касается​ работаете с очень​ Оно находится в​ не нужен)​ результатом.​​ и «Стоимость/Сумма». Установим​​Однако, когда таких таблиц​

​, например, так:​F4​ при помощи функции​

​ по такому сценарию,​​61​​ поподробнее последний аргумент,​
​ ссылкам, поскольку именованный​​ давайте рассмотрим несколько​​ значение и возвращает​

​(ROW).​ВПР​ большими объёмами данных,​ третьем столбце «AdwordsAds».​​тк один раз​​Изменяем материал – меняется​ денежный формат для​ много, функция​=Lemons Mar​​функция​​COUNTIF​

​ поскольку она возвратит​миля в час,​​ который указывается для​​ диапазон не меняется​​ примеров использования​​ соответствующее значение из​К сожалению, формулы с​, то обычно выделяют​​ состоящих из тысяч​​ЦитатаRAN написал:​ тут помогали, и​​ цена:​​ новых ячеек.​ЕСЛИ​… или наоборот:​НАИМЕНЬШИЙ({массив};1)​(СЧЁТЕСЛИ), учитывая, что​ первое найденное значение,​ хотя в списке​​ функции​​ при копировании формулы​ВПР​ другого столбца. Говоря​ВПР​

​ две причины ошибки​ строк, или когда​И формулы рабочие?Я​​ там было пустое​​Скачать пример функции ВПР​Выделяем первую ячейку в​– это не​=Mar Lemons​возвращает​ имена клиентов находятся​ соответствующее заданному искомому​ есть также​ВПР​ в другие ячейки.​

ВПР в Excel – это нужно запомнить!

1. ​в формулах с​​ техническим языком,​​перестают работать каждый​#ЗНАЧ!​ искомое значение вписано​ заархивировал и залил​ поле, в Интервальном​ в Excel​​ столбце «Цена». В​​ лучшее решение. Вместо​
2. ​Помните, что имена строки​​1-й​​ в столбце B:​ значению. Например, если​Гепард​–​ Значит, Вы можете​
3. ​ реальными данными.​ВПР​ раз, когда в​.​​ в формулу.​​ основной файл. Что​​ просмотре)​​Так работает раскрывающийся список​
4. ​ нашем примере –​​ нее можно использовать​​ и столбца нужно​​(наименьший) элемент массива,​​=B2&COUNTIF($B$2:B2,B2)​​ Вы хотите узнать​​(Cheetah), который бежит​​range_lookup​​ быть уверены, что​На практике формулы с​ищет значение в​​ таблицу поиска добавляется​​Будьте внимательны: функция​Если Вы используете формулу​​ бы меньше морочиться,​​буду знать)​
5. ​ в Excel с​ D2. Вызываем «Мастер​​ функцию​​ разделить пробелом, который​ то есть​=B2&СЧЁТЕСЛИ($B$2:B2;B2)​ количество товара​ со скоростью​(интервальный_просмотр). Как уже​ диапазон поиска в​
6. ​ функцией​ первом столбце заданного​ или удаляется новый​ВПР​ с условием поиска​ можно посмотреть его.​
7. ​Hugo​ функцией ВПР. Все​ функций» с помощью​​ДВССЫЛ​​ в данном случае​​1​​После этого Вы можете​Sweets​70​

​ упоминалось в начале​ формуле всегда останется​​ВПР​​ диапазона и возвращает​ столбец. Это происходит,​не может искать​ приближённого совпадения, т.е.​JayBhagavan, в смысле,​​: Последний параметр 1​​ происходит автоматически. В​ кнопки «fx» (в​(INDIRECT), чтобы возвратить​ работает как оператор​. Для ячейки​ использовать обычную функцию​, заказанное покупателем​миль в час,​

​ урока, этот аргумент​ корректным.​
​редко используются для​
​ результат из другого​

​ потому что синтаксис​

office-guru.ru

Продвинутые примеры с функцией ВПР: поиск по нескольким критериям

​ значения, содержащие более​ аргумент​​ на это надо​​ или true или​ течение нескольких секунд.​ начале строки формул)​ нужный диапазон поиска.​ пересечения.​​F5​​ВПР​Jeremy Hill​ а 70 ближе​ очень важен. Вы​Если преобразовать диапазон ячеек​ поиска данных на​ столбца в той​ВПР​ 255 символов. Если​range_lookup​ заменить? Только ошибка​ вообще его нет​ Все работает быстро​​ или нажав комбинацию​​Как Вы, вероятно, знаете,​При вводе имени, Microsoft​

• ​возвращает​, чтобы найти нужный​
• ​, запишите вот такую​ к 69, чем​ можете получить абсолютно​
• ​ в полноценную таблицу​ том же листе.​
• ​ же строке.​требует указывать полностью​
• ​ искомое значение превышает​(интервальный_просмотр) равен TRUE​
• ​ от этого никуда​ — это тогда​

Поиск в Excel по нескольким критериям

​ и качественно. Нужно​​ горячих клавиш SHIFT+F3.​​ функция​ Excel будет показывать​2-й​ заказ. Например:​ формулу:​ 61, не так​ разные результаты в​ Excel, воспользовавшись командой​ Чаще всего Вы​В самом привычном применении,​ весь диапазон поиска​ этот предел, то​ (ИСТИНА) или не​ не денется​

Пример 1: Поиск по 2-м разным критериям

​ , когда нужно​ только разобраться с​ В категории «Ссылки​​ДВССЫЛ​​ подсказку со списком​наименьший элемент массива,​​Находим​​=VLOOKUP(B1,$A$5:$C$14,3,FALSE)​​ ли? Почему так​​ одной и той​Table​ будете искать и​ функция​ и конкретный номер​ Вы получите сообщение​

​ указан, Ваша формула​​Мне нужны данные​​ найти ближайшее значение​ этой функцией.​ и массивы» находим​используется для того,​ подходящих имен, так​ то есть​2-й​=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)​​ происходит? Потому что​​ же формуле при​​(Таблица) на вкладке​​ извлекать соответствующие значения​ВПР​

​ столбца для извлечения​
​ об ошибке​

​ может сообщить об​ с листа «Объявления​​ из существующих, и​​Keles​​ функцию ВПР и​​ чтобы вернуть ссылку,​ же, как при​

​3​товар, заказанный покупателем​– эта формула вернет​ функция​ его значении​Insert​​ из другого листа.​​ищет в базе​​ данных. Естественно, и​​#ЗНАЧ!​ ошибке​ гугл» в листе​ вот тогда нужен​: День добрый уважаемые​ жмем ОК. Данную​ заданную текстовой строкой,​​ вводе формулы.​​, и так далее.​Dan Brown​

​ результат​ВПР​TRUE​(Вставка), то при​Чтобы, используя​​ данных заданный уникальный​​ заданный диапазон, и​.​#Н/Д​ «ОбъявленияЯндекс». А именно:​​ сортированный список.​​ форумчане. Столкнулся с​ функцию можно вызвать​

​ а это как​
​Нажмите​

​INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3))​

​:​
​15​

​при поиске приблизительного​​(ПРАВДА) или​​ выделении диапазона мышью,​ВПР​​ идентификатор и извлекает​​ номер столбца меняются,​​Решение:​​в двух случаях:​​ значения колонки «Headline»​​Если же нужно​ тем что Впр​ перейдя по закладке​

Пример 2: ВПР по двум критериям с просматриваемой таблицей на другом листе

​ раз то, что​Enter​ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3))​=VLOOKUP(«Dan Brown2»,$A$2:$C$16,3,FALSE)​, соответствующий товару​ совпадения возвращает наибольшее​FALSE​ Microsoft Excel автоматически​, выполнить поиск на​ из базы какую-то​ когда Вы удаляете​Используйте связку функций​Искомое значение меньше наименьшего​

​ из «ОбъявленияГугл» подтягивается​ точное — то​ отказывается сопоставить данные=)))​ «Формулы» и выбрать​ нам сейчас нужно.​и проверьте результат​Функция​=ВПР(«Dan Brown2»;$A$2:$C$16;3;ЛОЖЬ)​Apples​

​ значение, не превышающее​​(ЛОЖЬ).​​ добавит в формулу​

​ другом листе Microsoft​
​ связанную с ним​

​ столбец или вставляете​ИНДЕКС+ПОИСКПОЗ​ значения в просматриваемом​ ВПР-ом в «Заголовок»,​ 0, и список​​ В чем моя​​ из выпадающего списка​ Итак, смело заменяем​В целом, какой бы​

​INDEX​Находим​, так как это​ искомое.​Для начала давайте выясним,​ названия столбцов (или​

​ Excel, Вы должны​
​ информацию.​

​ новый.​(INDEX+MATCH). Ниже представлена​ массиве.​ а «Description line​ может быть в​ ошибка?​ «Ссылки и массивы».​ в представленной выше​ из представленных выше​(ИНДЕКС) просто возвращает​3-й​ первое совпадающее значение.​Надеюсь, эти примеры пролили​ что в Microsoft​

​ название таблицы, если​​ в аргументе​​Первая буква в названии​​Решение:​ формула, которая отлично​Столбец поиска не упорядочен​ 1″ и «Description​ беспорядке.​Serge_007​Откроется окно с аргументами​ формуле выражение с​ методов Вы ни​

​ значение определённой ячейки​товар, заказанный покупателем​Есть простой обходной путь​ немного света на​ Excel понимается под​ Вы выделите всю​table_array​ функции​И снова на​ справится с этой​ по возрастанию.​​ line 2″, с​​hitman1316​​: В столбце А​​ функции. В поле​

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

​ функцией​​ выбрали, результат двумерного​​ в массиве​Dan Brown​ – создать дополнительный​ работу с функцией​ точным и приближенным​ таблицу).​(таблица) указать имя​ВПР​ помощь спешат функции​ задачей:​Если Вы ищете точное​ листа «ОбъявленияГугл», должны​: Спасибо большое)​ у Вас текст,​

​ «Искомое значение» -​ЕСЛИ​ поиска будет одним​C2:C16​:​ столбец, в котором​ВПР​ совпадением.​Готовая формула будет выглядеть​ листа с восклицательным​

​(VLOOKUP) означает​ИНДЕКС​=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))​​ совпадение, т.е. аргумент​​ подтягиваться в единое​serlas77​ в F -​ диапазон данных первого​​на ссылку с​​ и тем же:​​. Для ячейки​​=VLOOKUP(«Dan Brown3»,$A$2:$C$16,3,FALSE)​ объединить все нужные​в Excel, и​​Если аргумент​​ примерно вот так:​ знаком, а затем​В​

​(INDEX) и​
​=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))​

​range_lookup​ поле «Текст» на​​: Добрый день, коллеги!​​ числа​ столбца из таблицы​

• ​ функцией​​Бывает так, что основная​​F4​​=ВПР(«Dan Brown3»;$A$2:$C$16;3;ЛОЖЬ)​​ критерии. В нашем​

  ​ Вы больше не​
​range_lookup​

• ​=VLOOKUP(«Product 1»,Table46[[Product]:[Price]],2)​​ диапазон ячеек. К​​ертикальный (​​ПОИСКПОЗ​​Если Вы извлекаете данные​

  ​(интервальный_просмотр) равен FALSE​
​ листе "ОбъявленияЯндекс" и​

​В исходном файле​Устранить можно так,​ с количеством поступивших​ДВССЫЛ​ таблица и таблица​функция​На самом деле, Вы​

​ примере это столбцы​​ смотрите на неё,​​(интервальный_просмотр) равен​=ВПР(«Product 1»;Table46[[Product]:[Price]];2)​ примеру, следующая формула​V​

​(MATCH). В формуле​
​ из другой рабочей​

​ (ЛОЖЬ) и точное​

• ​ между ними должен​​ есть несколько таблиц.​ только умножать надо​ материалов. Это те​. Вот такая комбинация​
• ​ поиска не имеют​​ИНДЕКС($C$2:$C$16;1)​​ можете ввести ссылку​​Имя клиента​
• ​ как на чужака.​​FALSE​А может даже так:​ показывает, что диапазон​ertical). По ней Вы​
• ​ИНДЕКС+ПОИСКПОЗ​​ книги, то должны​ значение не найдено,​ стоять пробел.​

​ Данные подтгяиваются через​ на 1​ значения, которые Excel​ВПР​ ни одного общего​возвратит​

​ на ячейку в​(Customer) и​ Теперь не помешает​​(ЛОЖЬ), формула ищет​​=VLOOKUP(«Product 1»,Table46,2)​A2:B15​ можете отличить​Вы раздельно задаёте​ указать полный путь​ формула также сообщит​JayBhagavan​​ ВПР в табличку​​Jhonson​ должен найти во​и​ столбца, и это​Apples​ качестве искомого значения​

Извлекаем все повторения искомого значения

​Название продукта​​ кратко повторить ключевые​​ точное совпадение, т.е.​=ВПР(«Product 1»;Table46;2)​находится на листе​ВПР​ столбцы для поиска​ к этому файлу.​ об ошибке​: Ошибся. Вместо «Слова»​ на листе «ОбъявленияЯндекс»​​: ну или так:​​ второй таблице.​​ДВССЫЛ​​ мешает использовать обычную​​, для​​ вместо текста, как​

​(Product). Не забывайте,​ моменты изученного нами​ точно такое же​При использовании именованных диапазонов,​ с именем​от​ и для извлечения​ Если говорить точнее,​

​#Н/Д​
​ д.б. "Заголовок". ВПР()​

​ (отформатирована, как таблица)​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(1*A:A;F:F;1;0)​Следующий аргумент – «Таблица».​отлично работает в​​ функцию​​F5​ представлено на следующем​ что объединенный столбец​ материала, чтобы лучше​ значение, что задано​ ссылки будут вести​Sheet2​ГПР​​ данных, и в​​ Вы должны указать​. Более подробно о​

​ Вы не верно​ из других отформатированных​Keles​ Это наш прайс-лист.​

Часть 1:

​ паре:​
​ВПР​

​функция​​ рисунке:​ должен быть всегда​ закрепить его в​ в аргументе​ к тем же​.​​(HLOOKUP), которая осуществляет​​ результате можете удалять​ имя рабочей книги​​ том, как искать​​ передали аргументы. В​ таблиц. Все данные,​: спасибо=) на будущее​​ Ставим курсор в​​=VLOOKUP($D$2,INDIRECT($D3&»_Sales»),2,FALSE)​. Однако, существует ещё​

​ИНДЕКС($C$2:$C$16;3)​​Если Вы ищите только​​ крайним левым в​ памяти.​​lookup_value​

Часть 2:

​ ячейкам, не зависимо​
​=VLOOKUP(40,Sheet2!A2:B15,2)​

​ поиск значения в​​ или вставлять сколько​​ (включая расширение) в​ точное и приближенное​ формулах я исправил​ кроме данных из​ буду знать​​ поле аргумента. Переходим​​=ВПР($D$2;ДВССЫЛ($D3&»_Sales»);2;ЛОЖЬ)​ одна таблица, которая​​возвратит​​2-е​​ диапазоне поиска, поскольку​​Функция​(искомое_значение). Если в​​ от того, куда​​=ВПР(40;Sheet2!A2:B15;2)​​ верхней строке диапазона​​ угодно столбцов, не​ квадратных скобках [​ совпадение с функцией​

Часть 3:

​ Ваши ошибки.​
​ таблицы с листа​

​Что текст и​​ на лист с​​Где:​​ не содержит интересующую​​Sweets​повторение, то можете​ именно левый столбец​ВПР​ первом столбце диапазона​ Вы копируете функцию​​Конечно же, имя листа​​ –​ беспокоясь о том,​ ], далее указать​​ВПР​​Цитатаserlas77 написал: Только​​ «ОбъявленияГугл», подтягиваются корректно.​​ цифры это я​​ ценами. Выделяем диапазон​​$D$2​ нас информацию, но​​и так далее.​​ сделать это без​​ функция​​в Excel не​​ t​​ВПР​ не обязательно вводить​​Г​​ что придётся обновлять​

Часть 4:

​ имя листа, а​
​.​

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

Часть 5:

​ знал как проверить(​
​ и ценами. Показываем,​

​ с названием товара,​ с основной таблицей​​ЕСЛИОШИБКА()​​ более сложную формулу:​просматривает при поиске​ Она всегда ищет​​(таблица) встречается два​​ книги.​ вводить формулу, а​H​ поиска.​ знак. Всю эту​ одно из самых​ точно. Избавьтесь от​

Двумерный поиск по известным строке и столбцу

​ формат данных, опечаток​ и исправить​ какие значения функция​ она неизменна благодаря​ и таблицей поиска.​В завершение, мы помещаем​=IFERROR(VLOOKUP($F$2,INDIRECT(«$B$»&(MATCH($F$2,Table4[Customer Name],0)+2)&»:$C16″),2,FALSE),»»)​ значения.​ значение в крайнем​

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

​ нет. Несколько раз​Serge_007​ должна сопоставить.​ абсолютной ссылке.​Давайте разберем следующий пример.​

Функции ВПР и ПОИСКПОЗ

​ формулу внутрь функции​=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(«$B$»&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&»:$C16″);2;ИСТИНА);»»)​​Итак, Вы добавляете вспомогательный​​ левом столбце диапазона,​​ совпадающих с аргументом​​ других функциях, в​ до аргумента​Функция​​ суть проблемы, правда?​​ в апострофы, на​​ВПР​​ «Слова» на листе​

​ пробовал пересоздавать с​
​: Функциями ЕТЕКСТ() и​

​Чтобы Excel ссылался непосредственно​$D3​​ У нас есть​​IFERROR​В этой формуле:​ столбец в таблицу​ заданного аргументом​lookup_value​ВПР​table_array​ВПР​Решение:​ случай если имя​это то, что​ «ОбъявленияЯндекс».​​ нуля файл на​​ ЕЧИСЛО()​ на эти данные,​​– это ячейка,​​ основная таблица (Main​(ЕСЛИОШИБКА), поскольку вряд​

​$F$2​
​ и копируете по​

​table_array​(искомое_значение), то выбрано​Вы можете использовать​

• ​(таблица), переключитесь на​доступна в версиях​​Всегда используйте абсолютные​​ книги или листа​
• ​ она не может​gling​ разных компах и​​Евгения​​ ссылку нужно зафиксировать.​
• ​ содержащая первую часть​ table) со столбцом​​ ли Вас обрадует​​– ячейка, содержащая​

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

​SKU (new)​ сообщение об ошибке​ имя покупателя (она​ формулу вида:​В функции​ них. Если совпадения​Знак вопроса (?) –​ выделите мышью требуемый​

Функция СУММПРОИЗВ

​ 2010, Excel 2007,​​ (с символом​​Вот полная структура функции​ столбец поиска в​

​ F1 по ВПР​
​ везде история повторяется.​

Функции ИНДЕКС и ПОИСКПОЗ

​ такая проблема. Функция​ «Таблица» и нажимаем​ нашем примере это​, куда необходимо добавить​#N/A​ неизменна, обратите внимание​

​=B2&C2​
​ВПР​

Именованные диапазоны и оператор пересечения

​ не найдены, функция​ заменяет один любой​ диапазон ячеек.​ Excel 2003, Excel​$​ВПР​ Вашей таблице должен​

1. ​ гласитЦитатаС помощью функции​​ В исходном, не​​ ВПР выдает значение​​ F4. Появляется значок​​FL​
2. ​ столбец с соответствующими​​(#Н/Д) в случае,​​ – ссылка абсолютная);​. Если хочется, чтобы​​все значения используются​​ сообщит об ошибке​ символ.​Формула, показанная на скриншоте​ XP и Excel​) при записи диапазона,​для поиска в​ быть крайним левым.​ ВПР можно выполнить​ усеченном файле, данные​ правильное когда забиваешь​
3. ​ $.​.​​ ценами из другой​​ если количество ячеек,​

   ​$B$​
   ​ строка была более​
   ​ без учета регистра,​

   ​#N/A​Звёздочка (*) – заменяет​ ниже, ищет текст​ 2000.​ например​ другой книге:​

​ На практике мы​ поиск в​ из таблички с​ саму функцию, но​В поле аргумента «Номер​_Sales​

1. ​ таблицы. Кроме этого,​​ в которые скопирована​​– столбец​

​ читаемой, можно разделить​ то есть маленькие​(#Н/Д).Например, следующая формула​ любую последовательность символов.​ «Product 1» в​Функция​

Используем несколько ВПР в одной формуле

​$A$2:$C$100​=VLOOKUP(lookup_value,'[workbook name]sheet name’!table_array, col_index_num,FALSE)​ часто забываем об​первом столбце диапазона​ листа «ОбъявленияГугл», не​ как только я​ столбца» ставим цифру​​– общая часть​​ у нас есть​ формула, будет меньше,​Customer Name​ объединенные значения пробелом:​ и большие буквы​ сообщит об ошибке​Использование символов подстановки в​

​ столбце A (это​ВПР​или​=ВПР(искомое_значение;'[имя_книги]имя_листа’!таблица;номер_столбца;ЛОЖЬ)​​ этом, что приводит​​(Диапазон. Две или​ находятся ВПР-ом, даже​ нажимаю ОК, вместо​ «2». Здесь находятся​ названия всех именованных​ 2 таблицы поиска.​ чем количество повторяющихся​;​=B2&» «&C2​​ эквивалентны.​​#N/A​ функциях​ 1-ый столбец диапазона​(VLOOKUP) имеет вот​$A:$C​​Настоящая формула может выглядеть​​ к не работающей​

​ более ячеек листа.​ если искомое значение​ значения у меня​ данные, которые нужно​ диапазонов или таблиц.​​ Первая (Lookup table​​ значений в просматриваемом​​Table4​​. После этого можно​

1. ​Если искомое значение меньше​​(#Н/Д), если в​​ВПР​ A2:B9) на листе​​ такой синтаксис:​​. В строке формул​​ так:​​ формуле и появлению​

   ​ Ячейки диапазона могут​
​ копипастить из самой​

   ​ прописана просто сама​​ «подтянуть» в первую​​ Соединенная со значением​​ 1) содержит обновленные​​ диапазоне.​​– Ваша таблица​​ использовать следующую формулу:​​ минимального значения в​​ диапазоне A2:A15 нет​может пригодиться во​Prices​VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])​

2. ​ Вы можете быстро​=VLOOKUP($A$2,'[New Prices.xls]Sheet1′!$B:$D,3,FALSE)​​ ошибки​​ быть как смежными,​ таблички с листа​ формула. В чем​ таблицу. «Интервальный просмотр»​ в ячейке D3,​ номера​​Выполнение двумерного поиска в​​ (на этом месте​

   ​=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)​
​ первом столбце просматриваемого​

   ​ значения​​ многих случаях, например:​​.​​ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])​​ переключать тип ссылки,​​=ВПР($A$2;'[New Prices.xls]Sheet1′!$B:$D;3;ЛОЖЬ)​​#Н/Д​​ так и несмежными.)​​ «ОбъявленияГугл», формула почему​ причина, почему значение​

​ — ЛОЖЬ. Т.к.​ она образует полное​SKU (new)​

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

​ Excel подразумевает поиск​ также может быть​=ВПР(«Jeremy Hill Sweets»;$A$7:$D$18;4;ЛОЖЬ)​ диапазона, функция​4​Когда Вы не помните​=VLOOKUP(«Product 1»,Prices!$A$2:$B$9,2,FALSE)​

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

​ обычный диапазон);​или​ВПР​:​ в точности текст,​=ВПР(«Product 1»;Prices!$A$2:$B$9;2;ЛОЖЬ)​ВПР​F4​

​ значение ячейки​Решение:​вернуть значение из любой​ их в упор.​ ячейку, а прописывается​​ а не приблизительные​​ Ниже приведены некоторые​​ а вторая (Lookup​​ номеру строки и​$C16​=VLOOKUP(B1,$A$7:$D$18,4,FALSE)​

​сообщит об ошибке​
​=VLOOKUP(4,A2:B15,2,FALSE)​

​ который нужно найти.​

• ​Пожалуйста, помните, что при​​в Microsoft Excel​.​A2​Если нет возможности​ ячейки​ Работает, только если​ сама формула?​ значения.​
• ​ подробности для тех,​​ table 2) –​ столбца. Другими словами,​– конечная ячейка​=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)​#N/A​=ВПР(4;A2:B15;2;ЛОЖЬ)​Когда Вы хотите найти​ поиске текстового значения​ имеет 4 параметра​
• ​Если Вы не хотите​​в столбце​​ изменить структуру данных​​в той же​​ интервальный просмотр =​Pelena​Нажимаем ОК. А затем​ кто не имеет​ названия товаров и​ Вы извлекаете значение​ Вашей таблицы или​Где ячейка​(#Н/Д).​​Если аргумент​​ какое-то слово, которое​ Вы обязаны заключить​

​ (или аргумента). Первые​ пугать пользователей сообщениями​​B​​ так, чтобы столбец​ строке диапазона.По факту​ истина, но в​: Скорей всего, формат​​ «размножаем» функцию по​​ опыта работы с​ старые номера​

​ ячейки на пересечении​ диапазона.​​B1​​Если 3-й аргумент​range_lookup​ является частью содержимого​ его в кавычки​ три – обязательные,​ об ошибках​на листе​ поиска был крайним​ Вы Ищите в​ этом случае данные​​ ячейки установлен Текстовый,​​ всему столбцу: цепляем​ функцией​​SKU (old)​​ конкретной строки и​​Эта формула находит только​​содержит объединенное значение​​col_index_num​​(интервальный_просмотр) равен​ ячейки. Знайте, что​

​ («»), как это​
​ последний – по​

​#Н/Д​

• ​Sheet1​​ левым, Вы можете​ первом, возвращаете из​ подтягиваются не корректно.​ сделайте Общий​
• ​ мышью правый нижний​​ДВССЫЛ​.​ столбца.​ второе совпадающее значение.​​ аргумента​​(номер_столбца) меньше​
• ​TRUE​​ВПР​ обычно делается в​ необходимости.​,​в рабочей книге​ использовать комбинацию функций​ третьего. Вам нужно​ Перечитал кучу тем​Hugo​ угол и тянем​.​Чтобы добавить цены из​​Итак, давайте обратимся к​​ Если же Вам​

Как работают ДВССЫЛ и ВПР

​lookup_value​1​​(ИСТИНА), формула ищет​​ищет по содержимому​

​ формулах Excel.​
​lookup_value​

​#ЗНАЧ!​New Prices​ИНДЕКС​ искать во втором,​ про ошибки ВПР,​: Формат ячейки должен​ вниз. Получаем необходимый​Во-первых, позвольте напомнить синтаксис​ второй таблицы поиска​

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

​(INDEX) и​ возвращать из третьего.​​ но решения своей​​ быть общий.​ результат.​ функции​ в основную таблицу,​

​ запишем формулу с​ повторения, воспользуйтесь предыдущим​4​ВПР​ сначала функция​ при включённой опции​table_array​ которое нужно искать.Это​#ИМЯ?​ значение из столбца​ПОИСКПОЗ​Pandora12​ проблемы так и​Костян​​Теперь найти стоимость материалов​​ДВССЫЛ​​ необходимо выполнить действие,​​ функцией​​ решением.​​– аргумент​сообщит об ошибке​ВПР​Match entire cell content​

​(таблица) желательно всегда​​ может быть значение​​, можете показывать пустую​D​(MATCH), как более​: Всегда с =ВПР​​ не нашел.​​: Добрый день!​ не составит труда:​(INDIRECT):​ известное как двойной​ВПР​Если Вам нужен список​​col_index_num​​#VALUE!​ищет точное совпадение,​​(Ячейка целиком) в​​ использовать абсолютные ссылки​

​ (число, дата, текст)​​ ячейку или собственное​​.​​ гибкую альтернативу для​​ () все получалось…​

​P.S. полный файл​У меня не​ количество * цену.​INDIRECT(ref_text,[a1])​ВПР​

​, которая найдет информацию​
​ всех совпадений –​

​(номер_столбца), т.е. номер​​(#ЗНАЧ!). Если же​​ а если такое​ стандартном поиске Excel.​ (со знаком $).​ или ссылка на​ сообщение. Вы можете​Если любая часть пути​​ВПР​​ и вот тебе​

​ весит больше разрешенного​ работает ВПР.​
​Функция ВПР связала две​
​ДВССЫЛ(ссылка_на_текст;[a1])​

​или вложенный​

office-guru.ru

Функция ВПР в Excel для чайников и не только

​ о стоимости проданных​ функция​ столбца, содержащего данные,​ он больше количества​ не найдено, выбирает​Когда в ячейке содержатся​

​ В таком случае​ ячейку (содержащую искомое​ сделать это, поместив​ к таблице пропущена,​.​

Как пользоваться функцией ВПР в Excel

​ на…​ на форуме значения,​Написал формулу, дальше​ таблицы. Если поменяется​Первый аргумент может быть​

​ВПР​ в марте лимонов.​ВПР​

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

​Другой источник ошибки​

1. ​Задача вот какая..​ поэтому файл пришлось​ скопировал «растяжением» -​ прайс, то и​ ссылкой на ячейку​.​
2. ​Существует несколько способов выполнить​тут не помощник,​Если Вам необходимо обновить​table_array​ – это наибольшее​ начале или в​ оставаться неизменным при​ возвращаемое какой-либо другой​в функцию​ВПР​#Н/Д​ справа есть массив​ залить архивом.​ 2 строки правильно​ изменится стоимость поступивших​ (стиль A1 или​Запишите функцию​ двумерный поиск. Познакомьтесь​
3. ​ поскольку она возвращает​ основную таблицу (Main​(таблица), функция сообщит​ значение, не превышающее​ конце содержимого. В​ копировании формулы в​ функцией Excel. Например,​ЕСЛИОШИБКА​не будет работать​в формулах с​
4. ​ данных (см. пример-приложение)​Спасибо!​ проставляет — остальные​ на склад материалов​ R1C1), именем диапазона​ВПР​ с возможными вариантами​ только одно значение​ table), добавив данные​ об ошибке​
5. ​ заданного в аргументе​ такой ситуации Вы​ другие ячейки.​ вот такая формула​(IFERROR) в Excel​ и сообщит об​ВПР​
6. ​ первый столбец -​Hugo​ #Н/Д​ (сегодня поступивших). Чтобы​ или текстовой строкой.​, которая находит имя​ и выберите наиболее​ за раз –​ из второй таблицы​#REF!​

​lookup_value​ можете долго ломать​Чтобы функция​ будет искать значение​ 2013, 2010 и​ ошибке​– это числа​

​ код страны, второй​: Можно увидеть файл​Форматы ячеек проверил,​

​ этого избежать, воспользуйтесь​ Второй аргумент определяет,​ товара в таблице​ подходящий.​ и точка. Но​ (Lookup table), которая​(#ССЫЛКА!).​(искомое_значение).​

1. ​ голову, пытаясь понять,​ВПР​
2. ​40​ 2007 или использовать​
3. ​#ЗНАЧ!​ в текстовом формате​ название страны -​
4. ​ с таблицами и​ текстовые и там​

​ «Специальной вставкой».​ какого стиля ссылка​

​Lookup table 1​

Быстрое сравнение двух таблиц с помощью ВПР

​Вы можете использовать связку​ в Excel есть​ находится на другом​Используйте абсолютные ссылки на​Если аргумент​ почему формула не​

1. ​работала между двумя​:​
2. ​ связку функций​(даже если рабочая​ в основной таблице​ все на английском..​ формулами? Этот не​ и там.​Выделяем столбец со вставленными​ содержится в первом​, используя​ из функций​ функция​ листе или в​ ячейки в аргументе​range_lookup​ работает.​ рабочими книгами Excel,​

​=VLOOKUP(40,A2:B15,2)​ЕСЛИ+ЕОШИБКА​ книга с таблицей​ или в таблице​

Функция ВПР в Excel с несколькими условиями

​ Слева — в​ смотрел.​Подскажите, пожалуйста, как​ ценами.​ аргументе:​SKU​ВПР​INDEX​ другой рабочей книге​table_array​(интервальный_просмотр) равен​

​Предположим, что Вы хотите​

​ нужно указать имя​=ВПР(40;A2:B15;2)​(IF+ISERROR) в более​ поиска в данный​ поиска.​ первом столбце -​gling​ заставить ее работать?​

​Правая кнопка мыши –​A1​, как искомое значение:​

1. ​(VLOOKUP) и​(ИНДЕКС), которая с​ Excel, то Вы​(таблица), чтобы при​
2. ​TRUE​ найти определенного клиента​
3. ​ книги в квадратных​Если искомое значение будет​ ранних версиях.​ момент открыта).​Это обычно случается, когда​

​ выпадающее меню с​

1. ​: Здравствуйте. В Вашем​
2. ​или какой-то другой​
3. ​ «Копировать».​

Функция ВПР и выпадающий список

​, если аргумент равен​=VLOOKUP(A2,New_SKU,2,FALSE)​ПОИСКПОЗ​ легкостью справится с​ можете собрать искомое​ копировании формулы сохранялся​(ИСТИНА) или не​ в базе данных,​

​ скобках перед названием​

1. ​ меньше, чем наименьшее​Синтаксис функции​Для получения дополнительной информации​
2. ​ Вы импортируете информацию​ названием страны (с​
3. ​ примере нет «Умных​ способ объединения этих​Не снимая выделения, правая​TRUE​
4. ​=ВПР(A2;New_SKU;2;ЛОЖЬ)​(MATCH), чтобы найти​

​ этой задачей. Как​ значение непосредственно в​ правильный диапазон поиска.​ указан, то значения​ показанной ниже. Вы​ листа.​ значение в первом​ЕСЛИОШИБКА​

1. ​ о функции​ из внешних баз​
2. ​ данными, которые берутся​ таблиц», и нет​ таблиц?​ кнопка мыши –​(ИСТИНА) или не​Здесь​ значение на пересечении​ будет выглядеть такая​ формуле, которую вставляете​
3. ​ Попробуйте в качестве​ в первом столбце​

​ не помните его​Например, ниже показана формула,​

​ столбце просматриваемого диапазона,​(IFERROR) прост и​

​ВПР​ данных или когда​ из правого крайнего​ правильно работающих формул.​Serge_007​ «Специальная вставка».​ указан;​New_SKU​ полей​

exceltable.com

Не работает ВПР?

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

​ говорит сам за​​, ссылающейся на другой​ ввели апостроф перед​ столба), после выбора​ Сохраните файл в​
​: Закрепить таблицу надо​Поставить галочку напротив «Значения».​R1C1​

​– именованный диапазон​​Название продукта​​ в следующем примере.​

​Как и в предыдущем​​ диапазоны или таблицы​ отсортированы по возрастанию,​
​ что она начинается​40​ВПР​ себя:​ файл Excel, обратитесь​

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

​ было:​​ ОК.​, если​$A:$B​(строка) и​Как упоминалось выше,​ примере, Вам понадобится​ в Excel.​ то есть от​ на «ack». Вот​на листе​сообщит об ошибке​IFERROR(value,value_if_error)​ к уроку: Поиск​ стоящий в начале​

​ должен пробиться код​​serlas77​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(A2;F$1:K$5;5;0)​Формула в ячейках исчезнет.​

​F​​в таблице​Месяц​

​ВПР​​ в таблице поиска​
​Когда выполняете поиск приблизительного​ меньшего к большему.​
​ такая формула отлично​Sheet2​#N/A​ЕСЛИОШИБКА(значение;значение_если_ошибка)​ в другой рабочей​
​ ноль.​ страны, вместо этого​: gling, Hugo,​
​Костян​ Останутся только значения.​
​ALSE​Lookup table 1​(столбец) рассматриваемого массива:​

​не может извлечь​​ (Lookup table) вспомогательный​ совпадения, не забывайте,​​ Иначе функция​

​ справится с этой​​в книге​(#Н/Д).​То есть, для первого​

​ книге с помощью​​Наиболее очевидные признаки числа​ выводится сообщение функции​
​Спасибо за быстрый​

​: Serge_007, при вставке​​​(ЛОЖЬ).​, а​=VLOOKUP(«Lemons»,$A$2:$I$9,MATCH(«Mar»,$A$1:$I$1,0),FALSE)​ все повторяющиеся значения​

​ столбец с объединенными​​ что первый столбец​ВПР​ задачей:​Numbers.xlsx​table_array​

​ аргумента Вы вставляете​​ ВПР.​ в текстовом формате​ =ВПР () «#н/д».​ отклик! Файл, о​

​ этой формулы пишет​​Функция помогает сопоставить значения​​В нашем случае ссылка​​2​=ВПР(«Lemons»;$A$2:$I$9;ПОИСКПОЗ(«Mar»;$A$1:$I$1;0);ЛОЖЬ)​ из просматриваемого диапазона.​ значениями. Этот столбец​ в исследуемом диапазоне​может вернуть ошибочный​=VLOOKUP(«ack*»,$A$2:$C$11,1,FALSE)​:​

​(таблица) – два​​ значение, которое нужно​Трудно представить ситуацию, когда​

​ показаны на рисунке​​ Не могу понять​

​ котором я говорил​​ ошибка в формуле.​​ в огромных таблицах.​​ имеет стиль​– это столбец​

​Формула выше – это​​ Чтобы сделать это,​ должен быть крайним​
​ должен быть отсортирован​ результат.​
​=ВПР(«ack*»;$A$2:$C$11;1;ЛОЖЬ)​=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)​ или более столбца​ проверить на предмет​ кто-то вводит значение​
​ ниже:​ в чем причина​ -​

excelworld.ru

Неправильно работает формула ВПР (Формулы)

​Serge_007​​ Допустим, поменялся прайс.​
​A1​ B, который содержит​ обычная функция​ Вам потребуется чуть​ левым в заданном​ по возрастанию.​Чтобы лучше понять важность​Теперь, когда Вы уверены,​
​=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)​ с данными.Запомните, функция​ ошибки, а для​

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

​ указывать второй аргумент​​ на рисунке выше)​​, которая ищет точное​​ составленная из нескольких​​Итак, формула с​​ важности четвертого аргумента.​TRUE​

​ имя, можно использовать​​ в Excel формулу​всегда ищет значение​

​ что нужно возвратить,​​, чтобы обозначить столбец,​
​ формате​ можно и без​
​ «ОбъявленияЯндекс» при подтяжке​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР(RC[-3];R1C[2]:R5C[7];5;0)​ новыми ценами.​ и сосредоточиться на​Запишите формулу для вставки​
​ совпадение значения «Lemons»​

​ функций Excel, таких​​ВПР​ Используйте значения​(ИСТИНА) или​ эту же формулу,​ с​ в первом столбце​ если ошибка найдётся.​ из которого нужно​General​
​ =ВПР () решить​ данных с листа​Барынина Ксения​В старом прайсе делаем​ первом.​

​ цен из таблицы​​ в ячейках от​

excelworld.ru

Не работает ВПР между двумя таблицами

​ как​​может быть такой:​
​TRUE​FALSE​ чтобы найти сумму,​ВПР​ диапазона, заданного в​Например, вот такая формула​ извлечь значение. Хотя​(Общий). В таком​ задачу ? Подскажите,​ «ОбъявленияГугл».​: Добрый день, serge​ столбец «Новая цена».​Итак, давайте вернемся к​Lookup table 2​ A2 до A9.​INDEX​=VLOOKUP(B2&» «&C2,Orders!$A&$2:$D$2,4,FALSE)​(ИСТИНА) или​(ЛОЖЬ), давайте разберём​ оплаченную этим клиентом.​, которая ссылается на​ аргументе​ возвращает пустую ячейку,​ это возможно, если​ случае есть только​ кто знает..​Z​ 007, я вам​Выделяем первую ячейку и​ нашим отчетам по​на основе известных​ Но так как​(ИНДЕКС),​=ВПР(B2&» «&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)​FALSE​ ещё несколько формул​ Для этого достаточно​ другую рабочую книгу:​table_array​ если искомое значение​ значение этого аргумента​
​ один заметный признак​sn_88​: serlas77, вас трудно​ на почту скинула​ выбираем функцию ВПР.​
​ продажам. Если Вы​

​ названий товаров. Для​​ Вы не знаете,​SMALL​Здесь в столбцах B​(ЛОЖЬ) обдуманно, и​

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

​: Вы не верно​​ понять, то вы​
​ вопрос с файликами,​ Задаем аргументы (см.​ помните, то каждый​ этого вставьте созданную​
​ в каком именно​(НАИМЕНЬШИЙ) и​ и C содержатся​ Вы избавитесь от​ВПР​

​ функции​​ не обязательно, но​ диапазоне могут быть​=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),»»)​ Excel, вложенной в​ по левому краю​ пользуетесь функцией впр!​ цепляете файл *.csv,​ надеюсь что поможете((​
​ выше). Для нашего​ отчёт – это​ ранее формулу в​ столбце находятся продажи​ROW​ имена клиентов и​

​ многих головных болей.​​и посмотрим на​
​ВПР​ так проще создавать​ различные данные, например,​=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)​ВПР​
​ ячейки, в то​Сравниваемое значение должно​
​ то отсылаете в​yderpleks​ примера: . Это​ отдельная таблица, расположенная​ качестве искомого значения​ за март, то​(СТРОКА)​ названия продуктов соответственно,​
​В следующих статьях нашего​ результаты.​на номер нужного​ формулу. Вы же​ текст, даты, числа,​Если Вы хотите показать​.​
​ время как стандартно​ соответствовать ЛЕВОМУ столбцу​ гуглдокс — неужели​: Я вот уже​ значит, что нужно​ на отдельном листе.​ для новой функции​ не сможете задать​Например, формула, представленная ниже,​
​ а ссылка​ учебника по функции​Как Вы помните, для​ столбца. В нашем​ не хотите вводить​

​ логические значения. Регистр​​ собственное сообщение вместо​Итак, если случилось, что​ они выравниваются по​

​ в выбранной вами​​ нет понятия, что​
​ все известные мне​ взять наименование материала​ Чтобы формула работала​ВПР​ номер столбца для​ находит все повторения​Orders!$A&$2:$D$2​ВПР​ поиска точного совпадения,​ случае это столбец​ имя рабочей книги​ символов не учитывается​ стандартного сообщения об​

​ аргумент​​ правому краю.​ таблице!​

​ здесь форум по​​ варианты устранения ошибки​ из диапазона А2:А15,​ верно, Вы должны​:​ третьего аргумента функции​ значения из ячейки​определяет таблицу для​в Excel мы​

​ четвёртый аргумент функции​​ C (3-й в​

​ вручную? Вдобавок, это​​ функцией, то есть​ ошибке функции​
​col_index_num​
​Решение:​Pandora12​ мелкомягкому экселю?!​

​ перепробовал, но не​​ посмотреть его в​ дать названия своим​=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)​ВПР​ F2 в диапазоне​ поиска на другом​
​ будем изучать более​
​ВПР​ диапазоне):​ защитит Вас от​ символы верхнего и​ВПР​
​(номер_столбца) меньше​Если это одиночное​: да, все верно..​Что — нет​ помогает(((​
​ «Новом прайсе» в​ таблицам (или диапазонам),​=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)​. Вместо этого используется​ B2:B16 и возвращает​ листе.​ продвинутые примеры, такие​должен иметь значение​=VLOOKUP(«ack*»,$A$2:$C$11,3,FALSE)​ случайных опечаток.​ нижнего регистра считаются​, впишите его в​1​ значение, просто кликните​ спасибо.. Пробелма решена​ более профильных ситуации​

​RAN​​ столбце А. Затем​ причем все названия​Здесь​ функция​ результат из тех​Чтобы сделать формулу более​
​ как выполнение различных​FALSE​=ВПР(«ack*»;$A$2:$C$11;3;ЛОЖЬ)​Начните вводить функцию​ одинаковыми.Итак, наша формула​ кавычках, например, так:​, функция​

​ по иконке ошибки​​ !​ форумов? Сюда -​: А поискать не​ взять данные из​ должны иметь общую​​Price​​ПОИСКПОЗ​ же строк в​ читаемой, Вы можете​ вычислений при помощи​(ЛОЖЬ).​Вот ещё несколько примеров​​ВПР​ будет искать значение​​=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),»Ничего не найдено. Попробуйте​ВПР​ и выберите​Vlad999​ к соседям -​ где светло, а​ второго столбца нового​

planetaexcel.ru

Функция =ВПР () не работает

​ часть. Например, так:​​– именованный диапазон​, чтобы определить этот​ столбце C.​ задать имя для​
​ВПР​Давайте вновь обратимся к​ с символами подстановки:​, а когда дело​40​ еще раз!»)​также сообщит об​Convert to Number​: сцепка функций =ИНДЕКС(….;ПОИСКПОЗ(…….);ПОИСКПОЗ(……))​ не заглядывали -​ где потеряли, не​ прайса (новую цену)​CA_Sales​$A:$C​ столбец.​{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,»»),ROW()-3)),»»)}​ просматриваемого диапазона, и​, извлечение значений из​ таблице из самого​~​ дойдёт до аргумента​в ячейках от​=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»Ничего не найдено. Попробуйте​ ошибке​(Конвертировать в число)​ или =ИНДЕКС(….;ПОИСКПОЗ(…….))​ ?!.​

​ пробовали?​​ и подставить их​,​
​в таблице​MATCH(«Mar»,$A$1:$I$1,0)​{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;»»);СТРОКА()-3));»»)}​ тогда формула станет​

​ нескольких столбцов и​​ первого примера и​Находим имя, заканчивающееся​table_array​

​A2​​ еще раз!»)​#ЗНАЧ!​

CyberForum.ru

​ из контекстного меню.​

На чтение 9 мин. Опубликовано 21.03.2020

Почему не работает ВПР в Excel?

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

Давайте постараемся вместе ответим на вопрос: «Почему функция ВПР не работает?»

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

Все остальные случаи связаны с тем, что мы что-то не учли или сделали неверно.

Итак, почему ВПР не находит значение, хотя оно есть?

1. Неточность при вводе данных.
2. Опечатка при вводе функции.
3. Неверные ссылки.
4. Неверно указан параметр «интервальный просмотр».
5. Столбец поиска не является первым слева столбцом диапазона поиска.
6. Несовпадение форматов данных.
   • Преобразуем число в текст.
   • Преобразуем текст в число.
   • Если числовой и текстовый форматы беспорядочно перемешаны?
7. Лишние пробелы и непечатаемые знаки.
8. Неправильно указан номер столбца.
9. Неверная ссылка на данные из другой таблицы
10. После изменения таблицы функция ВПР перестала работать
11. Как убрать сообщение #Н/Д в ВПР?

Неточность при вводе данных.

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

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

Опечатка при вводе функции.

Если вы видите ошибку #ИМЯ?, то это означает, что при записи названия самой функции вы допустили неточность – перепутали или добавили лишнюю букву.

Проверьте синтаксис, и всё будет в порядке.

Неверные ссылки.

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

Случается, что в формуле вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), а затем произвели какие-то действия с таблицей. К примеру, добавили столбец. Ваши ссылки в формуле теперь будут указывать на неверные координаты.

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

Неверно указан параметр «интервальный просмотр».

Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр = ИСТИНА или вовсе опущен), но на самом деле данные не отсортированы.

Об этой проблеме мы подробно говорили ранее.

Столбец поиска не является первым слева столбцом диапазона поиска.

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

Если ваши просматриваемые данные находятся не в первом, а во втором или другом столбце, то она не сможет найти ни одного совпадения, и вы вновь увидите сообщение #Н/Д.

Это важное ограничение нельзя забывать.

Несовпадение форматов данных.

Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.). Ведь число, записанное в таблицу Excel, может быть в двух принципиально разных состояниях — как число и как текст. И визуально их отличить практически невозможно.

Преобразуем число в текст.

Как видите, с виду записи одинаковы, однако в D6 артикул сохранен как число, а в A10 – как текст (на таких ячейках обычно видна пометка — зелёный уголок). Текст не может быть равнозначен числу, поэтому получаем #Н/Д. ВПР не находит значение, хотя оно с виду есть.

Как решить эту проблему со случайным несоответствием в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любой тип данных в текстовый вид.

Используем формулу

=ВПР(ТЕКСТ(D6;»#»);$A$3:$B$21;2;ЛОЖЬ)

Как видно, обычную ссылку на D6 мы заменяем конструкцией

TEXT(D6,»#»)

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

Можно поступить и проще — «приклеить» к числу пустую строку при помощи оператора склейки &.

=ВПР(D6&»»;$A$3:$B$21;2;ЛОЖЬ)

Запомните простое правило:

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

Если несовпадение форматов встречается редко, то можно просто исправить формат записи данных. Измените формат ячейки на текстовый и пересохраните ее содержание (F2 и затем ENTER). Содержимое будет преобразовано в текст.

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

Преобразуем текст в число.

А если нужно сделать обратное преобразование? Преобразовать число как текст в обычное число, чтобы все данные в таблице были одного типа? Давайте рассмотрим на примере.

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

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

Так же и в нашем случае. Текст преобразуется в число. Итак, наша формула выглядит следующим образом:

=ВПР(—A2;$D$2:$E$8;2;0)

Если числовой и текстовый форматы беспорядочно перемешаны?

А теперь — самый сложный случай. И здесь тоже есть простое решение.

Если поиск с преобразованием значения в число не удался, то будем искать его как текст. Объединяем оба варианта при помощи функции ЕСЛИОШИБКА.

=ЕСЛИОШИБКА(ВПР(—O2;$R$2:$S$8;2;0);ВПР(O2&»»;$R$2:$S$8;2;0))

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

Лишние пробелы и непечатаемые знаки.

Формула не работает, потому что в артикуле присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).

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

Такая неточность может встречаться довольно часто, и обнаружить её визуально практически невозможно. К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.

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

В этом случае можно использовать функции СЖПРОБЕЛЫ (TRIM)  и ПЕЧСИМВ (CLEAN) для удаления лишних пробелов либо других невидимых символов. Вместо

=ВПР(D8;$A$3:$B$21;2;ЛОЖЬ)

вводим

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(D8));$A$3:$B$21;2;ЛОЖЬ)

или

=VLOOKUP(TRIM(CLEAN(D8)),$A$3:$B$21,2,FALSE)

Функция СЖПРОБЕЛЫ убирает пробелы, а ПЕЧСИМВ удаляет все непечатаемые и невидимые символы.

Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных неточностей ввода данных.

Неправильно указан номер столбца.

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

Он не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если он указан неверно, то получим ошибку #ЗНАЧ!

Когда вы видите такое, пересчитайте количество колонок в диапазоне, который вы указали, и убедитесь, что оно не меньше, чем номер, указанный третьим аргументом функции. Быть может, вы указали 4, а колонок всего 3.

И в любом случае – это число должно быть больше 1. Вряд ли кто-то из вас будет руками вводить 0 или -1, но если вы получаете его при помощи каких-то вычислений, вложенных в формулу, то здесь-то и может быть проблема.

Неверная ссылка на данные из другой таблицы

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

Если вы случайно допустили неточность при указании ссылки на эти данные, то увидите сообщение #ССЫЛКА!.

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

После изменения таблицы функция ВПР перестала работать

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

Теперь же появилась ошибка #Н/Д либо результаты стали явно неверны. В чем дело?

А дело всё в том, что вы не использовали абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились, и все сломалось.

Кроме того, в третьем аргументе вы указываете порядковый номер столбца, из которого нужно извлечь данные. Но если вы вставили дополнительные колонки, то и количество столбцов, и их порядковые номера изменятся. А у вас в формуле используются «старые» номера.

Постарайтесь отменить сделанные изменения, благо Excel хранит всю историю изменений вашей таблицы.

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

Как убрать сообщение #Н/Д в ВПР?

Если функция не находит точного совпадения или искомого объекта просто нет в таблице, то она выдает сообщения, о которых мы говорили выше. С одной стороны, это хорошо, поскольку отрицательный результат- это тоже результат.

Однако, иногда это не очень удобно, т.к. такие сообщения потом порождают новые проблемы в расчете итогов, попадают в распечатку и т.д. Ведь если на эту ячейку ссылаются какие-то другие формулы, то они будут неправильно работать.

Можно легко перехватить ошибки и заменить их на любое другое подходящее нам значение (например, на ноль) с помощью функции ЕСЛИОШИБКА.

Синтаксис её подобен ЕСЛИ. В качестве условия используется выражение, которое потенциально может возвратить сообщение об ошибке.

=ЕСЛИОШИБКА([выражение];[значение_если_ошибка])

Задача – заменить его числом или выражением, указанным во втором аргументе. Рассмотрим это на примере.

В нашем случае маракуйя отсутствует в прайсе. Но вместо #Н/Д мы выведем ноль.

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

=ЕСЛИОШИБКА(ВПР(A4;$D$2:$E$7;2;0);0)

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

Надеемся, теперь вы сможете ответить на вопрос: «Почему не работает функция ВПР в Excel?».

[the_ad_group id=»48″]

Еще полезные примеры:

Исправление ошибки #Н/Д

​Смотрите также​​Vlad999​Как-то это можно​ игрушки :)​Функция не может найти​ означает, что разрешен​ Для этого выделите​ начале сообщит об​ и G27. Как​ значение, которое больше​ того, в случае​ выводить результат. Самый​).​ ниже показано, как​ вас установлена текущая​ точное совпадение, укажите​Ошибка #Н/Д из-за​Примечание:​:​ сделать?​Где ошибка? Возможно,​

​ нужного значения, потому​ поиск только​ все ячейки прайс-листа​

Лучшее решение

​ ошибке, т.к. не​ сделать чтобы в​ искомого, то она​ несортированного списка, ВПР() с​ левый столбец (ключевой)​Минимальное значение аргумента​ выглядит диаграмма со​ версия Office 365, и​ для аргумента​ разных типов данных​

​ Мы стараемся как можно​Fairuza​

​предпочитаемый способ решения​ у Вас она​ что в коде​точного соответствия​ кроме «шапки» (G3:H19),​

​ обнаружит этот метод.​​ этих ячейках или​ выводит значение, которое​ параметром​ имеет номер 1​номер_столбца​ значениями 0 и​ вы пользуетесь каналом​интервальный_просмотр​

• ​Решение​ оперативнее обеспечивать вас​,​ — формулы​ возникает из-за отсутствия​

​ присутствуют пробелы или​, т.е. если функция​ выберите в меню​ Хотя код написан​ вообще ничего не​

​ расположено на строку​Интервальный_просмотр​ (по нему производится​равно 1. При​ #Н/Д.​ выпуска программы предварительной​значение ЛОЖЬ. Помните,​. Убедитесь, что типы​ актуальными справочными материалами​

​Hugo121​​Michael_S​ параметра функции «интервальный_просмотр».​ невидимые непечатаемые знаки​ не найдет в​Вставка — Имя -​

​ будет верно. Вместо​ было или нули​ выше его. Как​ИСТИНА (или опущен)​ поиск).​ этом значение 1 соответствует​

Неправильные типы значений

​В предыдущем примере значения 0​ оценки с ранним​ что значение ИСТИНА,​ данных совпадают. Проверьте​ на вашем языке.​, сейчас попробую) спасибо)​: В С1​Guest​

​ (перенос строки и​ прайс-листе укзанного в​

​ Присвоить (Insert -​​ FileSearch в версиях​ стояли, вместо #Н/Д?​ следствие, если искомое​ работать не будет.​Параметр ​ столбцу поиска, значение 2 —​​ показаны в виде​​ доступом, можно ввести​​ сообщающее функции о​​ форматы ячеек. Для​ Эта страница переведена​Fairuza​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));;ВПР(A1;B:B;1;ЛОЖЬ))​

​: Ошибка возникла после​​ т.п.). В этом​ таблице заказов нестандартного​ Name — Define)​ 2007 и выше​Заранее благодарен!​​ значение меньше минимального​​В файле примера лист Справочник​​интервальный_просмотр​​ первому столбцу справа​​ прямой линии вдоль​​ формулу в верхней​

В ячейках есть лишние пробелы

​ том, что нужно​ этого выделите диапазон​ автоматически, поэтому ее​,​и протянуть вниз​ того, как я​ случае можно использовать​ товара (если будет​или нажмите​ приходится применять рекурсию.​JeyCi​

​ в ключевом столбце,​ также рассмотрены альтернативные​может принимать 2​

​ от столбца поиска​​ нижнего края диаграммы,​ левой ячейке диапазона​ искать приблизительное совпадение,​ ячеек, щелкните правой​ текст может содержать​Hugo121​yuslnt​ пересортировал исходный список​ текстовые функции​ введено, например, «Кокос»),​CTRL+F3​Мотя​​: =ЕСЛИОШИБКА(ВПР(D27;таблица;2;ЛОЖЬ);»»)​​ то функцию вернет​ формулы (получим тот​ значения: ИСТИНА (ищется​ и т. д. Поэтому при​ а затем линия​ вывода и нажать​ может привести к​ кнопкой мыши, выберите​ неточности и грамматические​,вышло вот так -​​: Михаил, спасибо.​​ в алфавитном порядке​СЖПРОБЕЛЫ (TRIM)​ то она выдаст​и введите любое​: Если макрос представляет​Deok1982​ ошибку ​ же результат) с​ значение ближайшее к критерию​

Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)

​ поиске в столбце​ резко поднимается вверх,​ клавишу​ возвращению не только​Формат ячеек​ ошибки. Для нас​ теперь пишет ?ИМЯ))​​Пока не разобрался​​ (где-то я нашел,​и​ ошибку #Н/Д (нет​ имя (без пробелов),​ собой​: , вашу формулу​#Н/Д.​​ использованием функций ИНДЕКС(),​​ или совпадающее с ним)​ A значение 1 указывает​ чтобы показать итог.​ВВОД​ ошибки #Н/Д, но​ >​ важно, чтобы эта​Код =ЕСЛИОШИБКА(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0);0;ВПР(A7;’Данные​ как это работает,​ что оно должно​ПЕЧСИМВ (CLEAN)​

​ данных).​ например​прикладную​ ввел в F27,​

​Найденное значение может быть​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ и ЛОЖЬ (ищется значение​ на него, значение 2 —​ В следующем примере​, чтобы подтвердить использование​ и ошибочных результатов,​Число​ статья была вам​ (2)’!$C$6:$E$40;2;0)) что тут​ но это работает.​ так быть. но​для их удаления:​Если введено значение​Прайс​ценность, проблему всегда​ теперь вместо #Н/Д​ далеко не самым​ ключевой столбец (столбец​ в точности совпадающее​ на столбец B,​ вместо нулевых значений​ формулы динамического массива.​ как видно в​(или нажмите клавиши​ полезна. Просим вас​ не так? пробовала​

​Michael_S​ после этого где-то​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​​1​​. Теперь в дальнейшем​ можно решить.​ появилось #Имя?​ ближайшим. Например, если​​ с артикулами) не​​ с критерием). Значение ИСТИНА​​ значение 3 — на столбец​​ используются значения #Н/Д.​

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

​ Иначе формулу необходимо​ следующем примере.​ CTRL+1) и при​ уделить пару секунд​ и этим вариантом​: что б было​ опять с той​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​или​ можно будет использовать​МВТ​Pelena​ попытаться найти ближайшую​ является самым левым​ предполагает, что первый​

​ C и т. д.​Исправление ошибки #Н/Д в​ вводить с использованием​

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

​ и если(еош(формула),0, формула)).​ более понятно, эта​ же строчки начинаются​Для подавления сообщения об​ИСТИНА (TRUE)​

​ это имя для​: Мотя, хотя бы​: Попробуйте так​

​ цену для 199,​​ в таблице, то​ столбец в​Задать вопрос на форуме​ функции ВПР​ прежней версии массива,​ ошибку из-за применения​ формат.​ ли она вам,​Veronka​ формула идентична вот​ не те результаты.​ ошибке​​, то это значит,​​ ссылки на прайс-лист.​ тем, что ВПР()​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»»;ВПР(D27;таблица;2;ЛОЖЬ))​ то функция вернет​ функция ВПР() не​таблице​ сообщества, посвященном Excel​Исправление ошибки #Н/Д в​ выбрав диапазон вывода,​ аргумента приблизительного совпадения​​Совет:​​ с помощью кнопок​: Код =ЕСЛИ(ЕНД(ВПР(A7;’Данные (2)’!$C$6:$E$40;2;0));0;ВПР(A7;’Данные​ этой​ Закономерность отследить не​#Н/Д (#N/A)​ что Вы разрешаете​Теперь используем функцию​ в этой ситуации​МВТ​

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

​ 150 (хотя ближайшее​ применима. В этом​отсортирован в алфавитном​У вас есть предложения​ функциях ИНДЕКС и​ введя формулу в​ в неотсортированной таблице​ Если вам нужно принудительно​

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

​ внизу страницы. Для​ (2)’!$C$6:$E$40;2;0))​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕНД(ВПР(A1;B:B;1;ЛОЖЬ));0;ВПР(A1;B:B;1;ЛОЖЬ))​ удается.​в тех случаях,​ поиск не точного,​ВПР​ придется вычислять дважды.​: Или установите Excel​ все же 200).​ случае нужно использовать​ порядке или по​

Пользовательская функция, которую вы ввели, недоступна

​ по улучшению следующей​ ПОИСКПОЗ​ левой верхней ячейке​В этом примере возвращается​ изменить формат для​

Выполняемый макрос использует функцию, которая возвращает значение «#Н/Д».

​ удобства также приводим​значит у вас​_Boroda_​с «интервальным_просмотром» вообще​ когда функция не​

При изменении защищенного файла, который содержит такие функции, как ЯЧЕЙКА, в ячейках выводятся ошибки #Н/Д

​ а​. Выделите ячейку, куда​ А если таблица​

Нужна помощь по аргументам функции?

​ 2007 или «старше»:​ Это опять следствие​ альтернативные формулы. Связка​ возрастанию. Это способ​ версии Excel? Если​К началу страницы​ диапазона и нажав​ не только ошибка​​ целого столбца, сначала​​ ссылку на оригинал​​ эксель 2003 и​​: Итоговую формулу по​

​ не совсем разобрался.​

​ может найти точно​приблизительного соответствия​ она будет введена​

Использование #Н/Д в диаграммах

​ объемная, это может​ до него функции​ того, что функция находит​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ используется в функции​ да, ознакомьтесь с​Примечание:​ клавиши​ #Н/Д для элемента​ примените нужный формат,​ (на английском языке).​ в нем нет​ двум столбцам можно​

​З.Ы. Ну игрушки,конечно,​ соответствия, можно воспользоваться​, т.е. в случае​ (D3) и откройте​ тормозить работу. Тем​ ЕСЛИОШИБКА() не было.​ наибольшее число, которое​ «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ по умолчанию, если​ темами на портале​

Дополнительные сведения об ошибке #Н/Д в конкретных функциях см. в следующих статьях:

• ​Мы стараемся как​CTRL+SHIFT+ВВОД​

• ​ «Банан», но и​ а затем выберите​Ошибка #Н/Д обычно означает,​

​ функции ЕСЛИОШИБКА​

support.office.com

Исправление ошибки #ЗНАЧ! в функции ВПР

​ переделать так:​​ но автоматизация процесса​ функцией​ с «кокосом» функция​ вкладку​ более, это не​Deok1982​ меньше или равно​В файле примера лист Справочник показано, что​ не указан другой.​ пользовательских предложений для​ можно оперативнее обеспечивать​для подтверждения. Excel​ неправильная цена для​Данные​ что формула не​Veronka​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—ЕЧИСЛО(ПОИСКПОЗ(A1;B:B;0))​ расчетов в данном​ЕСЛИОШИБКА​ попытается найти товар​Формулы — Вставка функции​

​ аргумент, а совет​: Pelena, в F27​ заданному.​ формулы применимы и​Ниже в статье рассмотрены​ Excel.​ вас актуальными справочными​ автоматически вставляет скобки​ элемента «Черешня». К​ >​

Проблема: длина аргумента искомое_значение превышает 255 символов.

​ находит запрашиваемое значение.​​: Проверьте так, правда​или, с любимым​ ключемне интереснее​(IFERROR)​ с наименованием, которое​

​ (Formulas — Insert​​Мотя​ стало пусто, но​Если нужно найти по​ для ключевых столбцов​ популярные задачи, которые​Функция ВПР(), английский вариант​ материалами на вашем​ в начале и​ такому результату приводит​Текст по столбцам​

Проблема: аргумент номер_столбца содержит текст или значение меньше 0.

​Чаще всего появление ошибки​ возможно с диапазонами​ многими ВПРом​DaffyMason​

​. Так, например, вот​ максимально похоже на​ Function)​: Птичку жалко…​​ в G27 появилось​​ настоящему ближайшее к​ содержащих текстовые значения,​ можно решить с​ VLOOKUP(), ищет значение​ языке. Эта страница​ конце формулы. Дополнительные​ аргумент ИСТИНА, который​ >​ #Н/Д обусловлено тем,​ промахнулась))) Код =СУММПРОИЗВ((A7=’Данные​​Code200?’200px’:»+(this.scrollHeight+5)+’px’);»>=—НЕ(ЕОШИБКА(ВПР(A1;B:B;1;0)))​​: Спасибо! Включение параметра​

​ такая конструкция перехватывает​​ «кокос» и выдаст​​. В категории​Безысходка…​ #ЗНАЧ!​ искомому значению, то ВПР() тут​ т.к. артикул часто​ использованием функции ВПР().​ в первом (в​ переведена автоматически, поэтому​ сведения о формулах​ сообщает функции ВПР,​Готово​ что формула не​

У вас есть вопрос об определенной функции?

​ (2)’!$C$6:$C$40)*’Данные (2)’!$D$6:$D$40)​Кстати, старайтесь не​

Помогите нам улучшить Excel

​ «Интервальный_просмотр» — ЛОЖЬ​ любые ошибки создаваемые​ цену для этого​Ссылки и массивы (Lookup​МВТ​Deok1982​ не поможет. Такого​

support.office.com

Функция ВПР() в MS EXCEL

​ бывает текстовым значением.​Пусть дана исходная таблица​ самом левом) столбце​ ее текст может​ массива см. в​ что нужно искать​.​ может найти значение,​

​Hugo121​ использовать в формулах​ помогло… результаты идут​ ВПР и заменяет​

​ наименования. В большинстве​ and Reference)​: Бесперспективняк — круче​: К сожалению, но​ рода задачи решены​ Также задача решена​ (см. файл примера​

Синтаксис функции

​ таблицы и возвращает​

​ содержать неточности и​​ статье Использование формул​ не точное, а​Начальные и конечные пробелы​ на которое ссылается​​: — не выбирает​​ столбец целиком. Если​ верные. с цифрами​ их нулями:​ случаев такая приблизительная​найдите функцию​The_Prist​ работаем на 2003,​ в разделе Ближайшее​​ для несортированного ключевого​​ лист Справочник).​

​ значение из той​​ грамматические ошибки. Для​ массива: рекомендации и​ приблизительное совпадение. Здесь​​ можно удалить с​​ функция ВПР, ГПР,​ значения, которые есть,​ формул много или​ посмотрю еще​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​ подстановка может сыграть​ВПР (VLOOKUP)​: Проблему вообще всегда​ т.к. макросы которые​​ ЧИСЛО. Там же можно​​ столбца.​Задача состоит в том,​​ же строки, но​​ нас важно, чтобы​ примеры.​​ нет близкого совпадения​​ помощью функции СЖПРОБЕЛЫ.​​ ПРОСМОТР или ПОИСКПОЗ.​ выводит только нули.​​ они сложные -​

​GIG_ant​​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​​ с пользователем злую​​и нажмите​ можно решить. Я​ когда то писали​ найти решение задачи​Примечание​ чтобы, выбрав нужный​

​ другого столбца таблицы.​​ эта статья была​​В данном случае для​ для элемента «Банан»,​ В приведенном ниже​ Например, искомого значения​— работает!!! спасибо))​ тормозить будет. Вместо​: дык где же​Если нужно извлечь не​ шутку, подставив значение​​ОК​​ про то, что​ в 2003, не​ о поиске ближайшего​. Для удобства, строка​ Артикул товара, вывести​Функция ВПР() является одной​

​ вам полезна. Просим​ месяцев с мая​ а «Черешня» предшествует​ примере в функции​

Задача1. Справочник товаров

​ нет в исходных​Veronka​ В:В можно, например,​

​ все таки ошибка​ одно значение а​ не того товара,​. Появится окно ввода​ не надо сразу​

​ работают в 2007​​ при несортированном ключевом​ таблицы, содержащая найденное​ его Наименование и​

​ из наиболее используемых​ вас уделить пару​​ по декабрь указано​​ элементу «Персик». В​ ВПР используется вложенная​ данных.​:​​ написать $B$1:$B$1000. Или​​ ??​ сразу весь набор​ который был на​ аргументов для функции:​ валить вину на​

​Deok1982​ столбце.​​ решение, выделена Условным форматированием.​​ Цену. ​

​ в EXCEL, поэтому​ секунд и сообщить,​ значение #Н/Д, поэтому​ этом случае при​ функция СЖПРОБЕЛЫ для​Элемент не найден​Fairuza​​ сделать динамический диапазон.​​DaffyMason​ (если их встречается​​ самом деле! Так​​Заполняем их по очереди:​ руки разработчика. Вы​: Pelena, вроде бы​Примечание​ (см. статью Выделение​Примечание​​ рассмотрим ее подробно. ​​ помогла ли она​

​ итог вычислить не​ использовании функции ВПР​ удаления начальных пробелов​ в исходных данных​,​Saff​: Я теперь не​ несколько разных), то​ что для большинства​

​Искомое значение (Lookup Value)​ же именно «кривыми​ разобрался, в вашу​. Для удобства, строка​ строк таблицы в​. Это «классическая» задача для​В этой статье выбран​ вам, с помощью​​ удается и вместо​​ с аргументом ЛОЖЬ​ из имен в​

​В данном случае в​Vlad999​: Добрый день!​ смогу воспроизвести тот​ придется шаманить с​ реальных бизнес-задач приблизительный​- то наименование​ руками» прокомментировали тот​ формулу «0» еще​ таблицы, содержащая найденное​ MS EXCEL в​ использования ВПР() (см.​ нестандартный подход: акцент​ кнопок внизу страницы.​ него отображается ошибка​ будет отображаться правильная​

​ ячейках A2:A7 и​ таблице подстановки нет​,​Помогите: Требуется вместо​ вариант исходного списка,​ формулой массива.​ поиск лучше не​ товара, которое функция​ факт, что код​

​ добавил​​ решение, выделена Условным форматированием.​ зависимости от условия​ статью Справочник).​ сделан не на​ Для удобства также​ #Н/Д.​ цена для элемента​ возврата названия отдела.​

​ элемента «Банан», поэтому​​Hugo121​ «#Н/Д» ставить «0»,​​ при котором функция​​Усовершенствованный вариант функции ВПР​ разрешать. Исключением является​ должна найти в​ перестал работать. Я​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;ЛОЖЬ));»0″;ВПР(D27;таблица;2;ЛОЖЬ))​ Это можно сделать​ в ячейке).​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​ саму функцию, а​ приводим ссылку на​

Задача2. Поиск ближайшего числа

​Чтобы исправить ошибку, проверьте​ «Черешня», но для​= ВПР (D2; TRIM​ функция ВПР возвращает​, День добрый, похожий​

​ если не находит​ выдавала #Н/Д​ (VLOOKUP 2).​ случай, когда мы​

1. ​ крайнем левом столбце​ привел аргумент из​Вроде все заработало​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​
2. ​Примечание​ значение параметра​ на те задачи,​
3. ​ оригинал (на английском​​ синтаксис используемой функции​​ элемента «Банан» все​ (A2: B7); 2;​

​ ошибку #Н/Д.​

​ вопрос. Если условие​ значение?​DaffyMason​Быстрый расчет ступенчатых (диапазонных)​

​ ищем числа, а​ прайс-листа. В нашем​ реальной жизни, когда​ как надо.Спасибо​Примечание​. Никогда не используйте​Интервальный_просмотр​ которые можно решить​ языке) .​ и введите все​ равно будет указана​ ложь)​Решение​ похоже, тоесть вместо​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(коэф!$E$2:$E$19;ПОИСКПОЗ(C4;коэф!$C$2:$C$19;))​: С цифрами тоже​ скидок при помощи​ не текст -​ случае — слово​ даже грамотный код​​V​

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

​ например, при расчете​ «Яблоки» из ячейки​ откажется работать при​: П.С. цифры можно​ столбце имеется значение​Интервальный_просмотр​ или ИСТИНА или​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​ функцией ВПР, весьма​ возвращают ошибку. Вероятно,​ что в списке​

​ 24 сентября 2018 г. (формулы динамического​​ значение есть в​ если не указано​:​V​Как сделать «левый ВПР»​

​ Ступенчатых скидок.​​ B3.​ переходе на более​ без кавычек писать.​ совпадающее с искомым,​ ИСТИНА (или опущен) если​​ вообще опустить). Значение​​Искомое_значение​ вероятно, что вы​ для проверки функции​ подстановок его нет.​ массива): если у​

​ исходных данных, или​ название конфеты, и​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=если(счетесли(коэф!$C$2:$C$19;C4);ИНДЕКС(коэф!$E$2:$E$19;ПОИСКПОЗ(C4;коэф!$C$2:$C$19;));»»)​: DaffyMason если прописываешь​ с помощью функций​Все! Осталось нажать​Таблица (Table Array)​

excel2.ru

Как избавиться от #н/д в excel при впр?

​ новую версию Excel.​​вместо ЛОЖЬ можно​
​ то функция с​
​ ключевой столбец не​
​ параметра ​- это значение,​ не раз сталкивались​ вам потребуется использовать​Если вы используете функцию​ вас установлена текущая​ используйте в формуле​ никак не могу​Saff​ интервальный_просмотр «ЛОЖЬ» сортировать​ ИНДЕКС и ПОИСКПОЗ​
​ОК​- таблица из​Кому лень или нет​ писать 0 (или​ параметром ​ отсортирован по возрастанию,​номер_столбца​ которое Вы пытаетесь​ с ошибкой #ЗНАЧ!.​ редактор Visual Basic.​ ПОИСКПОЗ, попробуйте изменить​ версия Office 365, и​ обработчик ошибок, например​ выкрутить, чтоб выводило​: Michael_S, спасибо большое!​ по порядку не​Как при помощи функции​и скопировать введенную​ которой берутся искомые​ времени читать -​
​ оставить пустой) покороче​

​Интервальный_просмотр​​ т.к. результат формулы​

​нужно задать =2,​​ найти в столбце​ В этом разделе​ Открыть этот редактор​ значение аргумента​

​ вы пользуетесь каналом​​ функцию ЕСЛИОШИБКА. Например,​
​ ноль, если нету​

​ всё работает )​​ обязательно. На будущее.​ ВПР (VLOOKUP) заполнять​ функцию на весь​ значения, то есть​

​ смотрим видео. Подробности​​ будет.​ =ЛОЖЬ вернет первое найденное​ непредсказуем (если функция ВПР()​ т.к. номер столбца​

​ с данными.​​ перечислены наиболее распространенные​ можно на вкладке​тип_сопоставления​ выпуска программы предварительной​ формула =ЕСЛИОШИБКА(ФОРМУЛА();0) означает​ нужного значения, тоесть​

​Чудовище​​ )))​ бланки данными из​ столбец.​ наш прайс-лист. Для​
​ и нюансы -​
​Юрий М​ значение, равное искомому,​

​ находит значение, которое​​ Наименование равен 2​Искомое_значение ​
​ проблемы с функцией​ «Разработчик» или с​, чтобы указать порядок​ оценки с ранним​

​ следующее:​​ если впр ищет​: Добрый день. Помогите,​

​Guest​​ списка​Функция​ ссылки используем собственное​ в тексте ниже.​: Это что за​ а с параметром​ больше искомого, то​ (Ключевой столбец всегда​может быть числом или​ ВПР и их​ помощью клавиш ALT+F11.​

​ сортировки таблицы. Чтобы​​ доступом, можно ввести​=ЕСЛИ(при вычислении формулы получается​
​ *Ромашку*, а в​

​ пожалуйста — если​​: Да я как-то​Как вытащить не первое,​ВПР (VLOOKUP)​

​ имя «Прайс» данное​​Итак, имеем две таблицы​
​ макросы такие?​ =ИСТИНА — последнее​ она выводит значение,​
​ номер 1). ​ текстом, но чаще​

​ возможные решения.​​Чтобы исправить ошибку, убедитесь​ найти точное совпадение,​

​ формулу в верхней​​ ошибка, то показать​ списке её нет.​ я при использовании​ вообще сей параметр​ а сразу все​возвращает ошибку #Н/Д​ ранее. Если вы​ -​Deok1982​ (см. картинку ниже).​ которое расположено на​Для вывода Цены используйте​ всего ищут именно​Решение​

​ в том, что​​ задайте для аргумента​ левой ячейке диапазона​​ 0, в противном​​Hugo121​ ВПР ищу не​

​ из вида упустил…​​ значения из таблицы​ (#N/A) если:​ не давали имя,​таблицу заказов​: , есть два​Если столбец, по которому​ строку выше его).​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​ число. Искомое значение должно​

​: Сократите значение или​​ книга, содержащая пользовательскую​
​тип_сопоставления​

​ вывода и нажать​​ случае показать результат​

​: СУММПРОИЗВ() даст 0,​​ 1-е, а 3-й​ сейчас таосвал список​Функции VLOOKUP2 и VLOOKUP3​Включен точный поиск (аргумент​ то можно просто​и​ файла екселевских. Сначала​ производится поиск не​Предположим, что нужно найти​номер_столбца​ находиться в первом​ используйте сочетание функций​ функцию, открыта, а​значение​ клавишу​ формулы)​

planetaexcel.ru

Использование функции ВПР (VLOOKUP) для подстановки значений

​ если нет данных,​ столбец?​ так и эдак​ из надстройки PLEX​Интервальный просмотр=0​

Постановка задачи

​ выделить таблицу, но​прайс-лист​​ запускается один, через​​ самый левый, то​​ товар, у которого​​нужно задать =3). ​

​ (самом левом) столбце​ ИНДЕКС и ПОИСКПОЗ​ функция работает правильно.​0​ВВОД​Вы можете указать «»,​ или же​

Решение

​=ВПР(C12;$A$1:$B$282;2;ЛОЖЬ)​ — все норм.​​DaffyMason​ ​) и искомого наименования​​ не забудьте нажать​​:​ ​ него второй. Во​​ ВПР() не поможет.​​ цена равна или​Ключевой столбец в нашем​ диапазона ячеек, указанного​ в качестве обходного​Чтобы исправить ошибку, убедитесь​(ноль).​, чтобы подтвердить использование​ чтобы не отображалось​Veronka​Как здесь исключить​Пойду на кальке​: Возникли своеобразные затруднения​

​ нет в​ потом клавишу​Задача — подставить цены​ втором появляется определенное​ В этом случае​ наиболее близка к​ случае содержит числа​ в​ пути.​​ в том, что​Чтобы исправить ошибку, убедитесь,​ формулы динамического массива.​​ ничего, или подставить​​: И СУММПРОИЗВ() не​​ Н/Д?​ теперь поизголяюсь​ с применением функции​​Таблице​​F4​ из прайс-листа в​ меню с добавлением​ нужно использовать функции​

​ искомой.​​ и должен гарантировано​​таблице​Примечание:​ аргументы функции верны​ что диапазон, на​​ Иначе формулу необходимо​ собственный текст: =ЕСЛИОШИБКА(ФОРМУЛА(),»Сообщение​ переносит строк в​​Читайте правила​​Я​ ВПР.​​.​​, чтобы закрепить ссылку​​ таблицу заказов автоматически,​​ объектов, с введением​​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​Чтобы использовать функцию ВПР()​

​ содержать искомое значение​

• ​.​​ Это формула массива, которую​ и расположены в​ который ссылается формула​ вводить с использованием​ об ошибке»)​ суммируемых данных.​Veronka​: в опен офисе​
• ​При создании файла​​Включен приблизительный поиск (​ знаками доллара, т.к.​ ориентируясь на название​ размеров и т.д.​Deok1982​ для решения этой​ (условие задачи). Если первый​Таблица -​ нужно вводить с​ нужных местах.​ массива, содержит такое​ прежней версии массива,​​Если вы не знаете,​​Fairuza​: День добрый, подскажите​ нет параметра интервальный​ в Excel 2003,​Интервальный просмотр=1​ в противном случае​ товара с тем,​ Так вот в​
• ​: Здравствуйте, Уважаемые Гуру​​ задачи нужно выполнить​ столбец не содержит искомый​ссылка на диапазон​ помощью клавиш CTRL+SHIFT+ВВОД.​Чтобы исправить ошибку, нажмите​ же количество строк​ выбрав диапазон вывода,​ что делать на​, а формула так​ как правильно сделать.​ просмотр. Как же​
• ​ когда функция ВПР​​), но​ она будет соскальзывать​ чтобы потом можно​ 2007 работать не​
• • ​Подскажите, пожалуйста.​​ несколько условий:​​ артикул​​ ячеек. В левом​​ Excel автоматически заключит​ клавиши CTRL+ALT+F9 для​ и столбцов, что​​ введя формулу в​​ этом этапе или​ и должна писаться​ есть перечень конфет​ быть???((​ ссылается на окно​Таблица​ при копировании нашей​ было посчитать стоимость.​ хочет​
  • ​Есть 4 ячейки.​​Ключевой столбец, по которому​​, ​​ столбце таблицы ищется ​​ формулу в фигурные​ пересчета листа.​ и диапазон ячеек,​ левой верхней ячейке​​ какого рода помощь​​ через запятые?​ и есть отдельный​vikttur​ с проверкой данных​, в которой происходит​ формулы вниз, на​В наборе функций Excel,​Deok1982​Ячейка D27 выпадающий​ должен производиться поиск,​то функция возвращает значение​Искомое_значение​ скобки {}. Если​Если вы не знаете​ в котором была​ диапазона и нажав​ вам нужна, поищите​Fairuza​ заказ на магазин​: Бежать на форум​ списком, на некоторые​ поиск не отсортирована​ остальные ячейки столбца​ в категории​

​: Вы имеете ввиду​​ список с размерами​​ должен быть самым​ ошибки​, а из столбцов​

Ошибки #Н/Д и их подавление

​ вы попытаетесь ввести​​ точно, какие аргументы​​ введена формула массива.​ клавиши​

• ​ похожие вопросы на​​, когда пользуюсь ,​​ (некоторые позичии). К​ по ОпенОфису.​​ значения функция либо​​ по возрастанию наименований.​
• ​ D3:D30.​​Ссылки и массивы​​ так?​​ (ширина подоконников), E27​​ левым в таблице;​ #Н/Д. ​ расположенных правее, выводится​
• ​ их вручную, Excel​ использовать, вам поможет​ Или введите формулу​CTRL+SHIFT+ВВОД​ форуме сообщества Excel​ выглядит вот так​ заказу нежно перетянуть​Z​ возвращает неверны результат​Формат ячейки, откуда берется​Номер_столбца (Column index number)​(Lookup and reference)​=ЕСЛИ(ЕНД(ВПР(D27;таблица;2;0));0;ВПР(D27;таблица;2;0))​ ячейка для ввода​Ключевой столбец должен быть​Это может произойти, например,​​ соответствующий результат (хотя,​​ отобразит формулу как​​ мастер функций. Выберите​​ массива в меньшее​для подтверждения. Excel​ или опубликуйте там​
  ​ Код =ЕСЛИ(ЕОШ(ВПР(A7;’06.2015′!$A$13:$I$165;3;0));0;ВПР(A7;’06.2015′!$A$13:$I$165;3;0))) ,​
• ​ стоимость конфет из​: Не следует быть​ (соседние строки), либо​ искомое значение наименования​- порядковый номер​имеется функция​V​ данных (длина подконников),​ обязательно отсортирован по​​ при опечатке при​​ в принципе, можно​​ текст.​​ ячейку в формуле,​
  ​ или большее число​
  ​ автоматически вставляет скобки​

​ свой вопрос.​ всё-равно выводит н/д-​​ перечня, но если​​ столь категоричным… Откройте​ ошибку #Н/Д. Перемена​ (например B3 в​ (не буква!) столбца​ВПР​​: да. это была​ ​ ячейка F27 с​​ возрастанию;​ вводе артикула. Чтобы не ошибиться​ вывести можно вывести​Номер столбца — это индекс​ которая вызывает у​

​ ячеек в соответствии​

​ в начале и​

P.S.

​Примечание:​ это как у​ там не указана​ в Кальке файл​ порядка в списке,​ нашем случае) и​ в прайс-листе из​

Ссылки по теме

• ​(VLOOKUP)​ инфа для общего​
• ​ формулой =ВПР(D27;таблица;2;ЛОЖЬ) и​Значение параметра ​ с вводом искомого​
• ​ значение из левого​ столбца, который нужно​ вас сомнения, затем​
• ​ со ссылкой на​ конце формулы. Дополнительные​ Если вам нужна справка​ кого настроен эксель,​
• ​ цена — выдаёт​ XL c формулой​ работа с числовыми​
• ​ формат ячеек первого​ которого будем брать​

planetaexcel.ru

Функция ВПР: неверный результат либо ошибка #Н/Д

​.​​ развития.​ в четвертой ячейке​Интервальный_просмотр​
​ артикула можно использовать Выпадающий​ столбца (в этом​ вернуть, отсчитываемый вправо​ откройте вкладку​ диапазон в формуле.​ сведения о формулах​ по ошибке #Н/Д​ русский вариант чаще​ н/д. нужно чтоб​ ВПР и тогда​ показателями ситуации не​ столбца (F3:F19) таблицы​ значения цены. Первый​Эта функция ищет​Мотя​ G27 получаю данные​ нужно задать ИСТИНА или​ список (см. ячейку ​ случае это будет​ от столбца поиска.​
​Формула​В данном примере ячейка​ массива см. в​

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

​ поймете, что ЕСТЬ…​​ улучшили. Аналогичная ситуация​

​ отличаются (например, числовой​​ столбец прайс-листа с​ заданное значение (в​
​: Однако, аргумент…​ =(E27*F27)*B4, где B4​ вообще опустить.​Е9​

​ на несовпадающие диапазоны:​​ массива: рекомендации и​ ИНДЕКС/ПОИСКПОЗ, щелкните здесь.​ если впр возвращает​ ЕСЛИ — не​ps Ключик -​

​ VLOOKUP в Open​​ случай особенно характерен​ 1, следовательно нам​ слово «Яблоки») в​

​ в EXCEL 2003​​Так вот, если​Для вывода найденной цены (она​Понятно, что в нашей​)). Часто левый столбец​ ошибку при вводе​

​.​​= SUM (если (A2:​ примеры.​

​Если вы хотите работать​​ ошибку (не нашлось),​ работает..​ в сортировке или​ Office Calc. как​ при использовании вместо​

​ нужна цена из​​ крайнем левом столбце​ комбинация ЕСЛИ(ЕОШИБКА(ВПР(…​ мы не вводим​ не обязательно будет​ задаче ключевой столбец​ называется​
​ аргумента​Excel автоматически запустит мастер.​

​ A11 = D2,​​По умолчанию функции, которые​ дальше, приведенный ниже​ то выводим 0​Hugo121​

​ ее отсутствии… ;)​​ ни странно, схожая​ текстовых наименований числовых​

​ столбца с номером​​ указанной таблицы (прайс-листа)​Работает распрекрасно хорошо​ значение в E27​ совпадать с заданной) используйте​ не должен содержать​ключевым​
​номер_столбца​
​Щелкните любой аргумент, и​ B2: B5))​ ищут данные в​ контрольный список поможет​

planetaexcel.ru

Замена значений ячеек #Н/Д (при исп. ВПР имею #Н/Д, с которой ничего не могу сделать)

​ ну или что​​: Попробуйте СУММПРОИЗВ() -​
​ -13529-​ конструкция в Excel​ кодов (номера счетов,​ 2.​ двигаясь сверху-вниз и,​ (см.)!​ и в D27​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ повторов (в этом​
​. Если первый столбец​или случайно указали​
​ Excel покажет вам​Чтобы формула вычислялась правильно,​

​ таблицах, должны использовать​​ вам определить возможные​
​ захотим, в другом​
​ он вернет 0,​

​yuslnt​​ 2010 Работает.​
​ идентификаторы, даты и​Интервальный_просмотр (Range Lookup)​ найдя его, выдает​

​Мотя​​ стоит какое-нибудь значение,​Как видно из картинки​ смысл артикула, однозначно​ не содержит ​
​ число менее 1​

​ сведения о нем.​​ необходимо изменить ее​ сортировку по возрастанию.​ причины проблем в​
​ случае пусть работает​
​ если нет цены​: Добрый день, коллеги!​
​Не исключаю, что​
​ т.п.) В этом​- в это​ содержимое соседней ячейки​: Значит, эти макросы​ тогда все нормально,​ выше, ВПР() нашла​ определяющего товар). В​искомое_значение​ в качестве значения​

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

​ упустилкакой-то момент. Подскажите​​ случае можно использовать​​ поле можно вводить​

​ (23 руб.) Схематически​​ писали «кривые» руки!​ в F27 показывается​

​ наибольшую цену, которая​​ противном случае будет​,​ индекса (такое часто​ пользу. Значения #Н/Д​ диапазона включали строки​ ВПР и ГПР​
​Искомое значение и исходные​
​P.S. я ошибся,​ нужно такую констррукцию​
​ ВПР для сравнения​

excelworld.ru

ВПР возвращает НД, если нет данных

​ пожалуйста!​​ функции​ только два значения:​ работу этой функции​The_Prist​ прайсовая цена по​ меньше или равна​ выведено самое верхнее​то функция возвращает​ происходит, если другая​ часто используются в​ 2–11.​ есть аргумент​ данные относятся к​ там ЕНД нужно​ = ЕСЛИОШИБКА(тут Ваша​ значений в столбцах,​

​Guest​​Ч​ ЛОЖЬ или ИСТИНА:​ можно представить так:​
​: Не факт. Если​ таблице, в G27​ заданной (см. файл​ значение.​

​ значение ошибки​​ функция Excel, вложенная​
​ диаграммах с такими​= SUM (если (A2:​интервальный_просмотр​

​ разным типам. Например,​​ использовать (у меня​​ формула;0)​​ появляется значение #Н/Д,​​: нужен небольшой пример​​и​
​Если введено значение​​Для простоты дальнейшего использования​​ использовался метод FileSearch,​​ стоит 0. Но​ примера лист «Поиск​
​При решении таких задач​ #Н/Д.​ в функцию ВПР,​ данными, как в​ A11 = D2,​

​, который сообщает функции,​​ вы пытаетесь использовать​ англ.версия, там ISNA​
​Veronka​ которое я не​ от вас​ТЕКСТ​

​0​​ функции сразу сделайте​ то он заблокирован​ стоит убрать выпадающий​ ближайшего числа»). Это​

​ ключевой столбец лучше​​Номер_столбца​ возвращает число, например​ приведенном ниже примере,​
​ B2: Б11))​

​ что нужно искать​​ ссылку на функцию​​ используется).​​: Используйте «если ошибка»​​ могу «превратить» в​​DaffyMason​​для преобразования форматов​​или​ одну вещь -​ в версиях старше​ список D27 (Delete)​ связано следует из​ предварительно отсортировать (это также​- номер столбца​ 0, в качестве​ поскольку эти значения​Примечание:​ точное совпадение, даже​ ВПР как число,​Hugo121​

​P.S. ЕСЛИОШИБКА() в​​ любое другое значение,​: вот эта табличка​ данных. Выглядеть это​

​ЛОЖЬ (FALSE)​​ дайте диапазону ячеек​ 2003. И поэтому​ как появляется #Н/Д​

​ того как функция​​ поможет сделать Выпадающий​Таблицы​ значения аргумента​
​ не отображаются на​​ 24 сентября 2018 г. (формулы динамического​ если таблица не​ а исходные данные​, да, через ЕНД​ 2003 нет, используйте​ с которым потом​vikttur​ будет примерно так:​
​, то фактически это​ прайс-листа собственное имя.​ код в самом​ в ячейках F27​ производит поиск: если функция ВПР() находит​ список нагляднее). Кроме​, из которого нужно​номер_столбца​
​ диаграмме. В примерах​ массива): если у​ отсортирована. Чтобы найти​ сохранены как текст.​ заработало)) спасибо​​ если(еош(формула),0, формула))​​ смогу работать.​: Ох уж эти​

CyberForum.ru

​=ВПР(ТЕКСТ(B3);прайс;0)​

Это вторая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня. Раз вы читаете эту главу, видимо, ваша ВПР возвращает ошибку #Н/Д. Этот симптом может быть вызван одной из нескольких проблем. Чтобы найти лекарство, посмотрите, что из следующего применимо к вашему случаю:

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

Разрозненные #Н/Д. Проблема: отдельные ВПР выдают ошибки #Н/Д (рис. 2.1).

Рис. 2.1. BG33-9 – это новый товар, которого нет в таблице подстановки

Скачать заметку в формате Word или pdf, примеры в формате Excel

Такие ситуации встречаются, когда искомое значение не найдено в первом столбце Таблицы. В нашем примере товар BG33-9 не найден в таблице постановки (в диапазоне F2:H29). Добавьте элемент BG33-9 в таблицу подстановки (рис. 2.2). Исправьте формулу ВПР, расширив диапазон таблицы =ВПР(A2;$F$2:$H$30;3;ЛОЖЬ).

Рис. 2.2. Новый товар добавлен в таблицу подстановки

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

• Вставьте новые ячейки в любом месте в середине таблицы подстановки. Например, выделите ячейки F28:H28, кликните правой кнопкой мыши, выберите опцию Вставить… и в открывшемся окне укажите Ячейки со сдвигом вниз.
• В качестве таблицы подстановки выберите не диапазон ячеек ($F$2:$H$29), а столбцы целиком – F:H. Теперь, вы можете добавлять элементы внизу таблицы подстановки без переписывания формул. Excel достаточно сообразителен, чтобы использовать только непустые ячейки при расчетах.
• Выберите любую ячейку в таблице подстановки и нажмите сочетание клавиш Ctrl+Т (Т английское). Это превращает диапазон в таблицу. При вводе новых значений в пустые строки ниже таблицы, она автоматически расширится. Теперь ваша ВПР будет ссылаться не на диапазон, а на ТАБЛИЦУ (рис. 2.3).

Рис. 2.3. Превратите диапазон таблицы подстановки в инструмент Excel ТАБЛИЦА; это позволит автоматически расширять его при добавлении новых строк

Чтобы не пропустить ошибки #Н/Д, отсортируйте данные по столбцу Описание по убыванию (рис. 2.4). Все ошибки соберутся вверху. Для этого выделите столбец D, перейдите на вкладку ДАННЫЕ и в области Сортировка и фильтры нажмите кнопку сортировки. В открывшемся окне выберите Автоматически расширить выделенный диапазон и кликните Сортировать.

Рис. 2.4. Сортировка по убыванию разместит все записи с ошибкой #Н/Д вверху диапазона

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

Существует несколько способов решить эту проблему. Если вы используете Excel 2007 или более позднюю версию, воспользуйтесь функциями ЕСЛИОШИБКА() или ЕСНД(). Если у вас более ранняя версия Excel (или требуется совместимость с более ранней версией) подойдет связка функций ЕСЛИ() и ЕНД().

Если исходная формула =ВПР(A67;$F$2:$H$29;3;ЛОЖЬ), то замените ее на =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0), или на =ЕСНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0).

Функция ЕСЛИОШИБКА() появилась в Excel 2007, что стало значительным улучшение по сравнению с предыдущими методами. С ЕСЛИОШИБКА() Excel сначала вычисляет формулу в первом аргументе функции. В 99% случаев возвращается правильный ответ, и работа функции останавливается. В тех редких случаях, когда возвращается ошибка, Excel переходит к обработке второго аргумента функции ЕСЛИОШИБКА(). В нашем примере, просто возвращает ноль.

Если вы предпочитаете в случае ошибки получить пустую ячейку, используйте формулу =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);""), но помните, что ячейка не будет по-настоящему пустой. Она будет содержать пустую текстовую строку, что, например, выдаст ошибку при попытке суммировать ее с другими числовыми значениями.

В более ранних версиях Excel 2007 используйте формулу: =ЕСЛИ(ЕНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ));0;ВПР(A67;$F$2:$H$29;3;ЛОЖЬ)).

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

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

Когда вы сталкиваетесь с такой проблемой, скорее всего, почти все ячейки содержат #Н/Д, а не некоторые, как в предыдущем разделе. Проблема может заключаться в том, что, либо элементы в столбце A, либо в столбце F имеют пробелы. Это может произойти, если вы импортировали данные в Excel из 1С, SAP и т.п. Чтобы диагностировать эту проблему, выделите ячейку А2 и нажмите клавишу F2, чтобы перевести ячейку в режим редактирования. Мигающий курсор вставки появится в конце ячейки (рис. 2.5). Если курсор вставки появляется сразу после последнего символа, всё в порядке. Если, как на рис. 2.5, значит присутствуют лишние пробелы. Аналогичную диагностику можно провести и для ячеек в столбце F.

Рис. 2.5. Ни одна из ВПР не работает, хотя совпадения наблюдаются

Воспользуйтесь функцией СЖПРОБЕЛЫ() для удаления начальных и конечных пробелов. Если есть пробелы между словами, функция изменит несколько пробелов подряд на один. Таким образом, формулу =ВПР(A3;$F$3:$G$30;2;ЛОЖЬ) замените на =ВПР(СЖПРОБЕЛЫ(A3);$F$3:$G$30;2;ЛОЖЬ).

Если лишние пробелы отображаются в таблице подстановки, вы можете воспользоваться формулой массива. Наберите с клавиатуры =ВПР(A3;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ). Но не жмите Enter после редактирования. Вместо этого, нажмите одновременно Ctrl + Shift + Enter (подробнее см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel). Ваша формула будет выглядеть так {=ВПР(A2;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ)}. Появятся фигурные скобки.

Подводный камень: формулы массива работают медленнее обычных формул. Это не проблема, если вы хотите произвести впечатление на коллег. Но в реальной жизни, лучше временно создать столбец, например, Н, в который ввести формулу =СЖПРОБЕЛЫ(F3). Затем скопировать его и вставить как значения в столбец F.

Числа и текст не распознаются как одинаковые. Проблема: часть значений ВПР работают корректно, а часть – возвращают ошибку (рис. 2.6).

Рис. 2.6. Часть значений ВПР возвращают ошибку

Вероятно, некоторые ячейки содержат числовые значения, а некоторые – числа, сохраненные как текст. На приведенном выше рисунке вы можете увидеть крошечный зеленый треугольник в левом верхнем углу ячеек В8, В11, В13. Если вы наведете курсор на ячейку, содержащую треугольник, слева появится символ с восклицательным знаком. Если раскрыть список, вы увидите, сообщение Число, сохраненное как текст (рис. 2.7).

Рис. 2.7. Сообщение: Число, сохраненное как текст

То, что числа хранятся в виде текста не обязательно плохо. Например, если это – модели продукта. Проблема в другом: формат данных в столбцах В и F – разный. Когда значение в B3 хранится в виде числа, а в F2 – в виде текста (см. рис. 2.6), ВПР не находит соответствия. Существуют функции, которые позволяют изменять формат данных. Однако, прежде чем их применить, нужно определить, в каком месте данные хранятся в виде чисел, а в каком – в виде текста. Для этого на свободном месте листа введите формулу =ЕЧИСЛО(В2) и =ЕЧИСЛО(F3). Значение ИСТИНА соответствует числу, ЛОЖЬ – тексту.

Если в столбце В хранится текст, а в столбце F – числа, используйте функцию ЗНАЧЕН, которая позволяет изменить текст на число. К сожалению, она выдаст ошибку, если наименование продукта буквенно-цифровое, например, 1120TS. Поэтому, нужно использовать конструкцию ЕСЛИОШИБКА вместе со ЗНАЧЕН (рис. 2.8).

Рис. 2.8. Преобразования текстовых значений в числовые

Если ситуация обратная, то можно использовать функцию ТЕСКТ. Буквенно-цифровые значения при этом не изменятся (рис. 2.9). Второй аргумент функции ТЕКСТ ("0") задает формат числа, на основании которого произойдет конвертация в текст (подробнее см. Пользовательский формат числа в Excel).

Рис. 2.9. Преобразования числовых значений в текстовые

Что делать, если проблема не укладывается ни в один из шаблонов? Т.е., когда, и столбец B, и столбец F, содержат комбинацию чисел и текста (рис. 2.10).

Рис. 2.10. Произвольное сочетание числовых и текстовых форматов в столбцах В и F

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

1. Выделите ячейку В2.
2. Нажмите Ctrl+Shift+Стрелка вниз. Выделится весь столбец В с данными.
3. Наберите Alt+Ы, не отпуская Alt, продолжайте набирать П, и наконец Г (или кликните на кнопке Готово). Это позволит преобразовать текстовые значения во всем столбце в числа.
4. Повторите пункты 1–3 для столбца F.

Примечание: сочетание клавиш Alt+Ы+П+Г – это быстрый способ выбрать все настройки по умолчанию в Мастере распределения текста по столбцам (вкладка ДАННЫЕ, область Работа с данными). Использование Мастера позволяет преобразовать все данные обратно в общий формат, что, как правило, решает проблему.

Альтернативные стратегии. Существуют и иные способы преобразования текста в числа. Самый простой способ собрать все текстовые ячейки рядом – отсортировать данные по столбцу B по убыванию. Ячейки с текстом попадут в начало списка. Если текстовых ячеек немного, их можно обработать вручную.

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

Контроль дубликатов при использовании ВПР. Проблема: у меня есть первоначальная таблица, и данные о продажах за 19.06.14. Я использовал функцию ВПР, чтобы извлечь продажи из списка, и отразить их в первоначальной таблице. Затем я получил файл с продажами за 20.06.14. Я воспользовался функцией ПОИСКПОЗ и нашел одного нового клиента в списке продаж 20.06.14 – Sun Life Fincl.

Рис. 2.11. Действительно ли это новый клиент?

Этого клиента новым не назовешь. Кто-то в департаменте закупок создал его вместо существующего клиента по имени Sun Life Financial. Чтобы исправить этот дубль, надо заменить в таблице Sun Life Fincl.  на Sun Life Financial. Это устранит ошибку #Н/Д. Однако, после этого исправления у вас появилось две строки, которые соответствуют Sun Life Financial.

ВПР не способна обрабатывать ситуации, подобные описанной. При наличии двух строк в таблице подстановки ВПР вернет продажи из первой попавшейся. Вы получите $3541, но не $2815.

Рис. 2.12. ВПР возвращает первое найденное значение

Если у вас нет полной уверенности, что клиенты в таблице подстановки уникальны, вы не должны использовать ВПР. Вместо этого используйте функцию СУММЕСЛИ. На рис. 2.13 использована следующая формула: =СУММЕСЛИ($A$2:$A$10;E2;$C$2:$C$10). Эта формула предписывает выполнять сканирование по столбцу Клиент (диапазон А2:А10), чтобы найти клиента, соответствующего значению в ячейке Е2. Каждый раз, когда в Excel находит соответствие Выручка из диапазона С2:С10 добавляется к значению в ячейке F2.

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

Параметр Искомое_значение должен содержать не более 255 символов. Проблема: ни одна из моих ВПР не работает. Я получаю ошибку #ЗНАЧ!

Чтобы выяснить, сколько знаков в ячейке A2, перейдите в любую пустую ячейку и введите =ДЛСТР(А2). ВПР не будет работать, если символов >255. В определенных обстоятельствах вы могли бы усечь содержимое ячейки с помощью формулы =ЛЕВСИМВ(A2;255). Но, иногда это не удастся, если ключи не уникальны при усечении до 255 символов.

Таблица подстановки имеет неверную ориентацию. Проблема: таблица подстановки расположена по горизонтали (рис. 2.14). Можно ли использовать ВПР?

Рис. 2.14. Таблица подстановки расположена по горизонтали

Стратегия: буква “В” в ВПР обозначает вертикальный просмотр. В Excel также есть функция ГПР для горизонтальных таблиц подстановки. Если вы хотите почудачить, то могли бы использовать формулу =ГПР(B3,$F$2:$Q$3,2,ЛОЖЬ).

Альтернативная стратегия: вы, скорее всего, поступите также, как и большинство других пользователей. Скопируете диапазон F2:В3, выделите ячейку F5, выполните специальную вставку, задав параметр Транспонировать. Это предаст таблице подстановки вертикальное положение. Затем вы введете обычную формулу с ВПР. Заметьте, что, начиная с версии 2010 в Excel для транспонирования используется специальный значок:

Рис. 2.15. Значок Транспонировать

Этот урок объясняет, как быстро справиться с ситуацией, когда функция ВПР (VLOOKUP) не хочет работать в Excel 2013, 2010, 2007 и 2003, а также, как выявить и исправить распространённые ошибки и преодолеть ограничения ВПР.

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

В этой статье Вы найдёте простые объяснения ошибок #N/A (#Н/Д), #NAME? (#ИМЯ?) и #VALUE! (#ЗНАЧ!), появляющихся при работе с функцией ВПР, а также приёмы и способы борьбы с ними. Мы начнём с наиболее частых случаев и наиболее очевидных причин, почему ВПР не работает, поэтому лучше изучать примеры в том порядке, в каком они приведены в статье.

• Исправляем ошибку #Н/Д
• Исправляем ошибку #ЗНАЧ! в формулах с ВПР
• Ошибка #ИМЯ? в ВПР
• ВПР не работает (проблемы, ограничения и решения)
• ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Исправляем ошибку #Н/Д функции ВПР в Excel

В формулах с ВПР сообщение об ошибке #N/A (#Н/Д) – означает not available (нет данных) – появляется, когда Excel не может найти искомое значение. Это может произойти по нескольким причинам.

1. Искомое значение написано с опечаткой

Хорошая мысль проверить этот пункт в первую очередь! Опечатки часто возникают, когда Вы работаете с очень большими объёмами данных, состоящих из тысяч строк, или когда искомое значение вписано в формулу.

2. Ошибка #Н/Д при поиске приближённого совпадения с ВПР

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

• Искомое значение меньше наименьшего значения в просматриваемом массиве.
• Столбец поиска не упорядочен по возрастанию.

3. Ошибка #Н/Д при поиске точного совпадения с ВПР

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

4. Столбец поиска не является крайним левым

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

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

5. Числа форматированы как текст

Другой источник ошибки #Н/Д в формулах с ВПР – это числа в текстовом формате в основной таблице или в таблице поиска.

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

Наиболее очевидные признаки числа в текстовом формате показаны на рисунке ниже:

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

Решение: Если это одиночное значение, просто кликните по иконке ошибки и выберите Convert to Number (Конвертировать в число) из контекстного меню.

Если такая ситуация со многими числами, выделите их и щелкните по выделенной области правой кнопкой мыши. В появившемся контекстном меню выберите Format Cells (Формат ячеек) > вкладка Number (Число) > формат Number (Числовой) и нажмите ОК.

6. В начале или в конце стоит пробел

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

Решение 1: Лишние пробелы в основной таблице (там, где функция ВПР)

Если лишние пробелы оказались в основной таблице, Вы можете обеспечить правильную работу формул, заключив аргумент lookup_value (искомое_значение) в функцию TRIM (СЖПРОБЕЛЫ):

=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)

Решение 2: Лишние пробелы в таблице поиска (в столбце поиска)

Если лишние пробелы оказались в столбце поиска – простыми путями ошибку #Н/Д в формуле с ВПР не избежать. Вместо ВПР Вы можете использовать формулу массива с комбинацией функций ИНДЕКС (INDEX), ПОИСКПОЗ (MATCH) и СЖПРОБЕЛЫ (TRIM):

=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))

Так как это формула массива, не забудьте нажать Ctrl+Shift+Enter вместо привычного Enter, чтобы правильно ввести формулу.

Ошибка #ЗНАЧ! в формулах с ВПР

В большинстве случаев, Microsoft Excel сообщает об ошибке #VALUE! (#ЗНАЧ!), когда значение, использованное в формуле, не подходит по типу данных. Что касается ВПР, то обычно выделяют две причины ошибки #ЗНАЧ!.

1. Искомое значение длиннее 255 символов

Будьте внимательны: функция ВПР не может искать значения, содержащие более 255 символов. Если искомое значение превышает этот предел, то Вы получите сообщение об ошибке #ЗНАЧ!.

Решение: Используйте связку функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH). Ниже представлена формула, которая отлично справится с этой задачей:

=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))

2. Не указан полный путь к рабочей книге для поиска

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

Вот полная структура функции ВПР для поиска в другой книге:

=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)

Настоящая формула может выглядеть так:

=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)

Эта формула будет искать значение ячейки A2 в столбце B на листе Sheet1 в рабочей книге New Prices и извлекать соответствующее значение из столбца D.

Если любая часть пути к таблице пропущена, Ваша функция ВПР не будет работать и сообщит об ошибке #ЗНАЧ! (даже если рабочая книга с таблицей поиска в данный момент открыта).

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

3. Аргумент Номер_столбца меньше 1

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

Итак, если случилось, что аргумент col_index_num (номер_столбца) меньше 1, функция ВПР также сообщит об ошибке #ЗНАЧ!.

Если же аргумент col_index_num (номер_столбца) больше количества столбцов в заданном массиве, ВПР сообщит об ошибке #REF! (#ССЫЛ!).

Ошибка #ИМЯ? в ВПР

Простейший случай – ошибка #NAME? (#ИМЯ?) – появится, если Вы случайно напишите с ошибкой имя функции.

Решение очевидно – проверьте правописание!

ВПР не работает (ограничения, оговорки и решения)

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

1. ВПР не чувствительна к регистру

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

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

2. ВПР возвращает первое найденное значение

Как Вы уже знаете, ВПР возвращает из заданного столбца значение, соответствующее первому найденному совпадению с искомым. Однако, Вы можете заставить ее извлечь 2-е, 3-е, 4-е или любое другое повторение значения, которое Вам нужно. Если нужно извлечь все повторяющиеся значения, Вам потребуется комбинация из функций ИНДЕКС (INDEX), НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW).

3. В таблицу был добавлен или удалён столбец

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

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

4. Ссылки на ячейки исказились при копировании формулы

Этот заголовок исчерпывающе объясняет суть проблемы, правда?

Решение: Всегда используйте абсолютные ссылки на ячейки (с символом $) при записи диапазона, например $A$2:$C$100 или $A:$C. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.

ВПР – работа с функциями ЕСЛИОШИБКА и ЕОШИБКА

Если Вы не хотите пугать пользователей сообщениями об ошибках #Н/Д, #ЗНАЧ! или #ИМЯ?, можете показывать пустую ячейку или собственное сообщение. Вы можете сделать это, поместив ВПР в функцию ЕСЛИОШИБКА (IFERROR) в Excel 2013, 2010 и 2007 или использовать связку функций ЕСЛИ+ЕОШИБКА (IF+ISERROR) в более ранних версиях.

ВПР: работа с функцией ЕСЛИОШИБКА

Синтаксис функции ЕСЛИОШИБКА (IFERROR) прост и говорит сам за себя:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

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

Например, вот такая формула возвращает пустую ячейку, если искомое значение не найдено:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")

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

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")

ВПР: работа с функцией ЕОШИБКА

Так как функция ЕСЛИОШИБКА появилась в Excel 2007, при работе в более ранних версиях Вам придётся использовать комбинацию ЕСЛИ (IF) и ЕОШИБКА (ISERROR) вот так:

=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)

Например, формула ЕСЛИ+ЕОШИБКА+ВПР, аналогична формуле ЕСЛИОШИБКА+ВПР, показанной выше:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))

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