Все формулы РФ

Excel и Google Workspace / Формулы Google Таблиц

VLOOKUP и XLOOKUP в Google Таблицах: поиск по ключу

VLOOKUP ищет ключ в первом столбце диапазона и возвращает значение из указанного столбца. XLOOKUP, если доступен, делает поиск гибче и понятнее.

Опубликовано: Обновлено:

Формулы Google ТаблицGoogle ТаблицыОфисные расчетыПоиск значенийСсылки на ячейкиКачество данныхЕсть историческая справка

Формула

$$=VLOOKUP(E2,A:B,2,FALSE)$$
Схема сопоставления Ключ и найденное значение

Код из рабочей строки соединяется со строкой справочника и возвращает цену.

Поиск по ключу надежен только тогда, когда ключи очищены и уникальны.

Обозначения

$E2$
ключ, который нужно найти в справочнике
$A:B$
диапазон справочника, где первый столбец содержит ключи
$2$
номер столбца в диапазоне, из которого возвращается результат VLOOKUP, столбец
$FALSE$
точное совпадение ключа

Условия применения

  • Для VLOOKUP ключ должен находиться в первом столбце выбранного диапазона.
  • Для рабочих справочников обычно используют точное совпадение FALSE, чтобы не получить ближайшее неверное значение.
  • Ключи должны быть одного типа и формата: текст с текстом, числа с числами, без лишних пробелов.

Ограничения

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

Подробное объяснение

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

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

XLOOKUP, если доступен в используемой среде, решает часть этих проблем: отдельно задаются диапазон поиска и диапазон результата, можно указать значение при отсутствии совпадения. Такая запись читается ближе к смыслу задачи: найти E2 в A:A и вернуть соответствующее значение из B:B. Но совместимость файла и привычки команды тоже важны.

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

Как пользоваться формулой

  1. Проверьте, что ключи в рабочей таблице и справочнике записаны в одном формате.
  2. Для VLOOKUP поставьте столбец ключа первым в выбранном диапазоне.
  3. Используйте FALSE для точного совпадения, если ищете артикулы, ID или коды.
  4. Обработайте отсутствие ключа через IFERROR или аргумент отсутствия в XLOOKUP.
  5. Проверьте справочник на дубликаты ключей перед публикацией отчета.

Историческая справка

Функции поиска стали важной частью электронных таблиц, когда файлы начали хранить не только расчеты, но и справочники. Пользователям нужно было соединять списки заказов, клиентов, товаров и тарифов без ручного копирования. VLOOKUP стал классическим инструментом вертикального поиска: найти ключ в первом столбце и вернуть значение справа. Со временем ограничения этого подхода стали очевидны, поэтому появились более гибкие варианты поиска, включая XLOOKUP в современных табличных средах. В Google Таблицах lookup-функции особенно важны вместе с импортами и совместными справочниками: данные могут жить на разных листах и обновляться несколькими людьми.

Историческая линия формулы

VLOOKUP и XLOOKUP не имеют одного автора как математические формулы. Это функции электронных таблиц для поиска по ключу, связанные с развитием офисных справочников и табличных моделей. Историческая атрибуция относится к эволюции spreadsheet lookup-подходов.

Пример

В справочнике A:B находятся артикулы и цены: в A - артикул, в B - цена. В E2 указан артикул заказа. Формула =VLOOKUP(E2,A:B,2,FALSE) ищет E2 в первом столбце A:B и возвращает цену из второго столбца. Если E2 равно SKU-17 и в справочнике этому артикулу соответствует 1250, результат будет 1250. Если артикул не найден, появится ошибка. Для аккуратного отчета ее можно обработать: =IFERROR(VLOOKUP(E2,A:B,2,FALSE),"Не найдено"). При доступности XLOOKUP запись может быть понятнее: =XLOOKUP(E2,A:A,B:B,"Не найдено").

Частая ошибка

Частая ошибка - забыть FALSE и получить приблизительный поиск там, где нужен точный артикул. Вторая ошибка - искать ключ не в первом столбце диапазона VLOOKUP. Третья ошибка - не заметить лишний пробел в ключе после импорта, из-за чего визуально одинаковые значения не совпадают. Еще одна проблема - дубликаты в справочнике: формула возвращает первое совпадение, но бизнес-логика может требовать актуальную цену, последний договор или все совпадения.

Практика

Задачи с решением

Подставить цену по артикулу

Условие. В E2 артикул заказа, в A:B справочник артикулов и цен. Нужно вернуть цену из второго столбца.

Решение. Ищем E2 в первом столбце диапазона A:B и возвращаем второй столбец с точным совпадением: =VLOOKUP(E2,A:B,2,FALSE).

Ответ. =VLOOKUP(E2,A:B,2,FALSE)

Показать Не найдено

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

Решение. Оборачиваем точный поиск в IFERROR: =IFERROR(VLOOKUP(E2,A:B,2,FALSE),"Не найдено").

Ответ. =IFERROR(VLOOKUP(E2,A:B,2,FALSE),"Не найдено")

Дополнительные источники

  • Google Docs Editors Help: Google Sheets function list - https://support.google.com/docs/table/25273?hl=en
  • Microsoft Support: XLOOKUP function - https://support.microsoft.com/en-gb/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
  • Microsoft Support: Excel functions by category - https://support.microsoft.com/en-au/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb

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

Excel и Google Workspace

IMPORTRANGE в Google Таблицах: данные из другой таблицы

$=IMPORTRANGE("spreadsheet_url","Лист1!A1:D100")$

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

Excel и Google Workspace

QUERY в Google Таблицах: базовый SELECT

$=QUERY(A1:D100,"select A, C where B = 'Оплачен'",1)$

QUERY выполняет запрос к диапазону Google Таблиц на языке, похожем на SQL. Базовый SELECT выбирает нужные столбцы и строки по условию.

Excel и Google Workspace

Поиск значения XLOOKUP / ПРОСМОТРX

$=XLOOKUP(E2,A:A,B:B)$

XLOOKUP ищет значение в одном диапазоне и возвращает соответствующее значение из другого диапазона. В русской локализации Excel функция может отображаться как ПРОСМОТРX.

Excel и Google Workspace

FILTER в Google Таблицах: выбрать строки по условиям

$=FILTER(A2:D100,B2:B100="Оплачен",C2:C100>=10000)$

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