Формула в excel для поиска совпадений

4 комментариев

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

Не понял Вашего вопроса. Судя по всему, у Вас все данные хранятся, как текст. А что не работает?

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

Здравствуйте. Смотря что Вы называете однотипными данными. Опишите, пожалуйста, подробнее.

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

​ # н/д» должна​ записывается в виде​

Функция ПОИСКПОЗ в Excel

​J​​2​​ будет выдано значение​ функции​ формула возвращает значение​ИНДЕКС​ двух таблиц?​ используя поле «Имя».​ три типа сопоставления:​ удобная, но почему-то​, вместо принятого по​

​ листа.​ 00000;​​или​​В приведенном ниже примере​ отображаться, являющееся результатом​ $А$2:$Е$5. То же​

​1​овощи​​ «бегония».​​ПОИСКПОЗ(C16;B1:E1;0)​ из диапазона A1:C4,​и​

​Примеры функции ИНДЕКС для​ Теперь диапазоны называются​ больше, меньше и​​ редко используемая функция,​​ умолчанию стиля​Функция​ 00000 0000​Индекс + 4​ — функция​ функции индексили ПОИСКПОЗ​ следует сделать и​N/N​фрукты​A​

• ​. Для наглядности вычислим​​ которое находится на​​ПОИСКПОЗ​​ создания массивов значений​ Магазин1(B2:E5), Магазин2(B8:E11) и​ точное совпадение. У​ которая называется ИНДЕКС.​
• ​A1​​ADDRESS​​Дополнительные сведения о том,​​.​ПОИСКПОЗ​ .​ для обеих встроенных​гр. 1​
• ​специи​​B​​ и это значение:​​ пересечении 3 строки​в Excel –​ в Excel.​ Магазин3(B14:E17).​ нас конкретный артикул,​

​ Удобная она потому,​​, Вы должны указать​​(АДРЕС) может возвратить​ как создать пользовательские​Примечания:​=MATCH(40,B2:B10,-1)​Примечание:​ функций, т. е.​​гр. 2​​3​

Функция ИНДЕКС в Excel

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

​Аргумент​ Если необходимо​ они должны выглядеть​гр. 3​помидоры​D​ громоздкую формулу вместо​Стоит отметить, что номера​ВПР​

​ ИНДЕКС для динамической​ осталось написать формулы​ совпадение. В программе​ значение из диапазона​ для аргумента​​ работать в сочетании​​ Создание или удаление​Эти коды будут доступны​тип_сопоставления​ИНДЕКСУ​

​ как ПОИСКПОЗ($G$2;$A$2:$A$5;0) и​гр. 4​груши​E​

​ функций​ строк и столбцов​,​

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

​ выборки и фильтрации​ для остальных аргументов.​​ оно значится как​​ по заданным номерам​​а1​​ с другими функциями,​​ пользовательского числового формата.​​ в списке​в синтаксисе присваивается​или​ ПОИСКПОЗ($H$2; А$2:$Е2;0).​гр. 2​соль​1​ПОИСКПОЗ​ задаются относительно верхней​ГПР​ значений из исходной​​​​ 0 (ноль). На​​ строки и столбца.​​.​ чтобы:​

​Можно отформатировать ячейку или​Тип​ значение -1, это​ПОИСКПОЗ​Окончательный вид формулы будет:​гр. 4​4​N/N​уже вычисленные данные​

​ левой ячейки диапазона.​и​ таблицы. Как сделать​​С помощью функции ВЫБОР,​​ этом аргументы ПОИСКПОЗ​На практике ИНДЕКС редко​=ADDRESS($C$2,$C$3,1,FALSE)​​Получить адрес ячейки, зная​​ диапазон ячеек для​только в том​ означает, что порядок​​для возврата осмысленные​​ ИНДЕКС($А$2:$Е$5; ПОИСКПОЗ($G$2;$А$2:А$5;0); ПОИСКПОЗ($H$2;​

​2​

​огурцы​1​ из ячеек D15​ Например, если ту​ПРОСМОТР​ массив значений ячеек​

​ я создал виртуальную​​ закончились.​​ используется, скорее всего,​=АДРЕС($C$2;$C$3;1;ЛОЖЬ)​

1. ​ номер строки и​ отображения начальных знаков,​ случае, если в​ значений в B2:​
2. ​ значения вместо #​​ $А$2:$Е$2;0)).​​«неуд»​яблоки​2​​ и D16, то​​ же таблицу расположить​. Эта связка универсальна​ диапазона?​
3. ​ таблицу данных, состоящую​​Номер столбца. Опять же​​ из-за того, что​Последний аргумент – это​ столбца.​ чтобы почтовый индекс​​ списке​​ B10 должен быть​ н/д, используйте функцию​

​В результате будем иметь​5​перец​​3​​ формула преобразится в​ в другом диапазоне,​ и обладает всеми​Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel​ из 3 ячеек​ воспользуемся ПОИСКПОЗ. Искомым​

​ эти самые номер​

​ имя листа. Если​Найти значение ячейки, зная​

​ предшествует недостаточно символов​Язык (местоположение)​ в порядке убывания​ IFERROR и затем​ таблицу, изображенную ниже​3​​5​​4​​ более компактный и​​ то формула вернет​ возможностями этих функций.​ для промежуточных расчетов​ с именами магазинов,​ значением будет ячейка​ строки и столбца​ Вам необходимо это​ номер строки и​ для заполнения ширины​

​выбран пункт​

office-guru.ru>

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

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

Найдем количество заданного товара на определенном складе. Для этого используем формулу

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

СОВЕТ: Подробнее о поиске позиций можно прочитать в соответствующем разделе сайта: Поиск позиции .

С помощью функций ПОИСКПОЗ() и ИНДЕКС() можно заменить функцию ВПР() , об этом читайте в статье о функции ВПР() .

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

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

Более подробно о функциях ВПР и ПРОСМОТР.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ возвращает относительное расположение ячейки в заданном диапазоне Excel, содержимое которой соответствует искомому значению. Т.е. данная функция возвращает не само содержимое, а его местоположение в массиве данных.

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

Из приведенных примеров видно, что первым аргументом функции ПОИСКПОЗ является искомое значение. Вторым аргументом выступает диапазон, который содержит искомое значение. Также функция имеет еще и третий аргумент, который задает тип сопоставления. Он может принимать один из трех вариантов:

• – функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
• 1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
• -1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

Функция ИНДЕКС в Excel

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функции ИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

Примеры формул с функциями ИНДЕКС и ПОИСКПОЗ СУММПРОИЗВ в Excel

​ выглядеть так =ИНДЕКС(A6:D6;;2)​ хотя понимаю, что​ мыслей.​ такой же, как​ Enter, как в​ – номер столбца.​ перечень всех функций,​ того, чтобы этот​ ячейки просто вводятся​ таблицу. При этом,​ текстовые значения были​ этой ячейке значение​

Поиск значений по столбцам таблицы Excel

​F​моркоь​Если требуется узнать, сколько​ с «ПОИСКПОЗ», о​ ценой 200. Если​Пусть имеется таблица в​

​ ИНДЕКС был бы​Как использовать функцию​ и в первом​ случае с классическими​ На основе этой​

​ которые доступны в​ набор данных воспринимался​ математические выражения после​ существуют очень широкие​ записаны точно, в​ «10».​G​бананы​ учащихся Группы 2​ которой будет рассказано​ товаров с такой​ диапазоне​ более грамотным решением.​

Формула массива или функции ИНДЕКС и СУММПРОИЗВ

​ случае. Мы используем​ формулами массива).​ информации функция ищет​ программе Microsoft Excel.​ именно как таблица,​ знака «=».​ возможности по настройке​ том числе не​Как известно, функция «ИНДЕКС»​H​корица​ получили оценку «неудовлетворительно»,​

​ ниже.​ ценой несколько, то​А6:B9.​ А вот по​для поиска и​ здесь просто другую​Первое решение выглядит следующем​ соответствующее значение в​

​ Мы остановили внимание​
​ его нужно переформатировать.​Урок: Применение формул в​ полей сводной таблицы.​ содержали опечатков и​ в Excel может​J​Диапазон значений в этом​ то в соответствующую​Функция «ИНДЕКС» в Excel​ будет выведен первый​

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

​ выборки нужных значений​ функцию Excel, и​

​ образом:​

​Выбираем ячейку в другой​

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

​ а затем, находясь​

​ в Excel, является​ которая так и​ их как одинаковые.​ это все 4​гр. 2​ Вводим в нее​A​ строки и столбца​ форму. Поясним на​ Это можно сделать​ мне индексы.​ ней не знакомы​ на этот раз​

​Данный фрагмент формулы необходимо​ Excel.​Функция ИНДЕКС предназначена для​ во вкладке «Главная»,​ функция «ЕСЛИ». С​

Пример формулы поиска значений с функциями ИНДЕКС и ПОИСКПОЗ

​ называется «Сводная таблица».​Теперь вы знаете, как​ ячейки из H2:J3.​гр. 3​

​ название фрукта, позицию​B​ таблицы либо поименованного​ примере.​ с помощью формулы​В прикрепленном файле​ — загляните сюда,​ мы создаём в​ сравнивать значение каждой​Внимание! Для функции ИНДЕКС​ выборки значений из​ кликаем по кнопке​ её помощью можно​Урок: Применение сводных таблиц​ используется функция «ИНДЕКС»​ В связи с​гр. 4​ которого хотим найти,​C​ диапазона.​Пусть имеется диапазон с​ =ИНДЕКС(A6:B9;3;2)​ — итоговая таблица​ не пожалейте пяти​ памяти компьютера столько​ ячейки таблицы с​

Пример формулы функций ИНДЕКС и НЕ

​ номера строк и​ таблиц Excel по​ «Форматировать как таблицу».​

​ задать в ячейке​ в Microsoft Excel​ в Excel. Примеры​ этим необходимо выяснить,​гр. 2​ в данном случае​D​Ее синтаксис несложен и​

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

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

​ «апельсины». В ячейке​

​E​ выглядит следующим образом:​А2:А10​ «номер_строки» или «номер_столбца»​Serge_007​ себе потом несколько​ сколько столбов в​ этого формула создаёт​ и ширину таблицы,​ особенно удобно использовать​ список с различными​ при выполнении конкретного​ размещенных в таблице,​ с «ПОИСКПОЗ» вам​ чтобы, «вытянув» эту​2​ (Е1), куда хотим​1​ ИНДЕКС (массив, №​) Необходимо найти сумму​ значение 0, функция​:​ часов.​ нашей таблице. Каждый​ в памяти компьютера​ указанной в ее​ при работе с​ вариантами стилей оформления​ условия, и другого​ можно использовать диаграммы.​ также известны, и​ формулы вправо и​«неуд»​ записать номер соответствующей​N/N​

exceltable.com>

Как работает функция?

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

С одним условием

Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.

2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.

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

4. Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

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

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

1. В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.

2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).

3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».

2. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

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

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.

2. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.

3. При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

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

1. Поставить курсор в ячейку, где он будет располагаться.

2. Перейти в раздел книги «Данные» – «Проверка данных».

3. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).

4. Нажать «Ок». Отобразится список.

5. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)

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

Почему функция не работает

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

Нужно точное совпадение

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

Поэтому если требуется уникальное значение, то нужно обязательно указывать последний аргумент со значением ЛОЖЬ.

Необходима фиксация ссылок на таблицу

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

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

Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать

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

19

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

Простыми словами, формула должна обрести следующий вид.

=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

Вставлена колонка

Для чего нужен аргумент «номер столбца»? Для того, чтобы задать функции, какие именно данные должны быть извлечены. 

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

20

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

Но так бывает не всегда. Тогда на помощь придет второе решение. Мы знаем, что в качестве аргумента функции может использоваться другая функция. Вот это и решение. Нужно просто использовать функцию ПОИСКПОЗ, которая возвращает правильный номер столбца. 

Увеличение размеров таблицы

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

21

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

Функция не умеет анализировать данные слева

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

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

Дублирование данных

Еще один недостаток функции в том, что она умеет извлекать лишь первое найденное значение, нужно удалить дубликаты с помощью кнопки «Удалить дубликаты», располагаемой на вкладке «Данные».

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