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

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

Что уже умеем

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

Теперь соберём это вместе: разберём VLOOKUP, гибкую связку INDEX и MATCH и функцию IFERROR.

Что вы узнаете в рамках статьи

В этом уроке вы:

• разберёте четыре аргумента VLOOKUP и напишете первый поиск;
• свяжете таблицу заказов со справочником и посчитаете выручку;
• освоите связку INDEX и MATCH и поймёте, чем она надёжнее;
• научитесь заменять ошибку #N/A понятным сообщением через IFERROR;
• узнаете, почему поиск иногда не находит существующее значение.

Ключевые термины

• Ключ поиска — значение, по которому вы ищете строку, например название товара.
• VLOOKUP — функция вертикального поиска: находит ключ в первом столбце диапазона и возвращает значение из указанного столбца той же строки.
• Точное совпадение — режим, при котором значение должно совпадать полностью; включается аргументом FALSE.
• IFERROR — функция, подменяющая ошибку формулы на понятное значение.
• MATCH — функция, возвращающая номер позиции найденного значения.
• INDEX — функция, возвращающая значение из диапазона по номеру позиции.
• #N/A — ошибка «значение не найдено»: поиск не дал результата.

Учебные данные: заказы и справочник товаров

Заведём на листе две таблицы. Справочник товаров — в столбцах H:J:

А таблицу заказов — в столбцах A:C, оставив D:F под расчёты:

Задача: подтянуть к каждому заказу цену и категорию, а затем посчитать суммы.

VLOOKUP: находим значение в справочнике

У функции VLOOKUP четыре аргумента:

=VLOOKUP(ключ; диапазон; номер_столбца; тип_совпадения)

Читается это так: «найди ключ в первом столбце диапазона и верни значение из столбца под таким номером в той же строке». Номер считается от левого края диапазона, а не листа: в H2:J6 столбец H первый, I второй, J третий.

Введите в D2 формулу для подстановки цены:

=VLOOKUP(B2; $H$2:$J$6; 3; FALSE)

В D2 появится 2400 — цена наушников. Скопируйте формулу до D6: получится 35, 320, 150, 890. Здесь важны две знакомые детали. Ключ B2 записан относительной ссылкой, потому что должен сдвигаться вместе с формулой. А диапазон справочника закреплён как $H$2:$J$6: оставь его относительным, при копировании он уедет вниз и последние строки перестанут находиться.

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

Категория подтягивается той же формулой, но с номером столбца 2: =VLOOKUP(B2; $H$2:$J$6; 2; FALSE). Осталось посчитать сумму умножением, =C2*D2. Итоговая таблица:

Главное в результате: справочник остался единственным местом, где хранятся цены. Поменяйте в J4 цену наушников на 2200 — и сумма заказа 1001 сразу станет 4400. Если справочник лежит на отдельном листе, диапазон записывается межлистовой ссылкой: =VLOOKUP(B2; 'Справочник'!$A$2:$C$6; 3; FALSE).

[Скриншот: лист с двумя таблицами и формулой VLOOKUP в формульной строке, диапазон справочника обведён рамкой]

INDEX и MATCH: гибкая замена VLOOKUP

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

Связка INDEX и MATCH от этого избавлена. По отдельности они работают так:

• MATCH ищет значение и возвращает его номер по порядку. =MATCH("Кружка"; H2:H6; 0) вернёт 5: кружка стоит пятой. Ноль в конце означает точное совпадение — аналог FALSE.
• INDEX достаёт значение по номеру. =INDEX(J2:J6; 5) вернёт 320 — пятое значение в столбце цен.

Соединим их: MATCH находит номер строки, а INDEX берёт из неё цену.

=INDEX($J$2:$J$6; MATCH(B2; $H$2:$H$6; 0))

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

Начинать удобнее с VLOOKUP, но в таблицах, которые живут долго и меняются, надёжнее сразу писать INDEX и MATCH.

IFERROR: что делать, если значение не найдено

Добавьте в B7 заказ на «Ежедневник», которого нет в справочнике: в D7 появится #N/A. Ошибка честная, но в отчёте выглядит пугающе и ломает подсчёт итогов.

Функция IFERROR подменяет ошибку на любое ваше значение. У неё два аргумента: формула и то, что показать вместо ошибки.

=IFERROR(VLOOKUP(B2; $H$2:$J$6; 3; FALSE); "Нет в справочнике")

Теперь в проблемной строке появится понятная подпись. Если для расчётов удобнее число, поставьте вместо текста 0 — тогда =SUM(F2:F7) посчитается без ошибок. Только не превращайте IFERROR в способ спрятать проблему: если ненайденных значений много, дело обычно не в справочнике, а в написании ключей, и тут пригодится TRIM.

Частые ошибки

• Незакреплённый диапазон справочника. Формула в первой строке работает, а ниже идут #N/A: при копировании диапазон уехал вниз. Пишите его абсолютной ссылкой.
• Ошибка #N/A из-за лишних пробелов. «Ручка» и «Ручка » для таблицы разные значения. Оберните ключ в TRIM или приведите в порядок справочник.
• Неверный номер столбца. Он считается от левой границы диапазона и не может превышать его ширину, иначе получите #REF!. Пересчитайте столбцы.
• Пропущенный аргумент FALSE. Без него VLOOKUP ищет приблизительно и на неотсортированном справочнике возвращает случайные значения. Опаснее всего то, что ошибки не будет — просто неверные числа.

Частые вопросы

• Что если в справочнике два одинаковых наименования?VLOOKUP и MATCH вернут значение из первой найденной строки, остальные проигнорируют. Поэтому ключи в справочнике должны быть уникальными.
• Можно ли искать по части названия?
  Да, звёздочка заменяет любое количество символов: =VLOOKUP("Науш*"; $H$2:$J$6; 3; FALSE) найдёт наушники. Для разового поиска удобно, но в постоянных формулах рискованно.
• Почему поиск по другому листу возвращает #REF!?
  Скорее всего, лист переименовали или удалили, и ссылка потеряла адресата. Проверьте имя листа в формуле и восстановите диапазон.

Итоги

Поздравляем, вы завершили модуль на самой востребованной теме. Теперь ваши таблицы умеют разговаривать друг с другом: вы связываете заказы со справочником через VLOOKUP, достаёте любой столбец по ключу, при необходимости заменяете эту функцию надёжной связкой INDEX и MATCH и обрабатываете ненайденные значения через IFERROR. Это тот навык, который отличает живую рабочую таблицу от простого списка: цены хранятся в одном месте, а отчёт пересчитывается сам. Оглянитесь на модуль — путь был большой: ссылки и диапазоны, условные функции, чистка текста и связывание таблиц.

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