Чтобы подтянуть значение из одной таблицы в другую, в Google Таблицах есть функция ВПР (по-английски VLOOKUP). Она ищет значение в первом столбце диапазона и возвращает ячейку из того же ряда в столбце с нужным номером. Рабочая формула выглядит так: =ВПР(F2;$A$2:$D$6;3;ЛОЖЬ). Читается она так: ищем значение из F2 в диапазоне A2:D6, ответ берём из третьего столбца, совпадение нужно точное (ЛОЖЬ).
Ниже разберём формулу по шагам на прайсе канцтоваров, ошибки #Н/Д и #REF!, поиск по другому листу и другому файлу. А если вы только знакомитесь с сервисом, сначала загляните в инструкцию по Google Таблицам для новичков.
CheckroiПодборка курсов по Google Sheets267 курсов • 32 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Как устроена функция ВПР в Google Таблицах
У ВПР четыре аргумента. Порядок такой же, как в Excel, поэтому формулу из ВПР в Excel можно перенести почти без правок.
| Аргумент | Что означает | В нашем примере |
|---|---|---|
| запрос | значение, которое ищем | F2, артикул «А-103» |
| диапазон | таблица, в первом столбце которой лежит запрос | $A$2:$D$6, прайс |
| индекс | номер столбца внутри диапазона, откуда брать ответ; считается от 1 | 3, «Цена» |
| отсортировано | ЛОЖЬ для точного совпадения, ИСТИНА для приблизительного | ЛОЖЬ |
Названия аргументов взяты из справки Google по функции VLOOKUP. Там же есть важная деталь: если четвёртый аргумент не указать, он по умолчанию принимает значение ИСТИНА, и функция ищет приблизительно. Поэтому ЛОЖЬ лучше писать всегда.
Искомое значение должно находиться в первом столбце диапазона, иначе функция вернёт ошибку. Ищете по артикулу, значит диапазон начинается со столбца артикулов, даже если левее есть другие данные.
Пошаговая инструкция: цена товара по артикулу
Возьмём маленький прайс: артикул, товар, цена и остаток в столбцах A–D, пять строк. Справа, в столбце F, перечислены артикулы, для которых нужно подставить цену и остаток.
- Встаньте в ячейку G2, куда должна попасть цена.
- Введите
=ВПР(и щёлкните по ячейке F2 с артикулом «А-103». - Поставьте точку с запятой и выделите прайс A2:D6. Чтобы диапазон не сползал при протягивании, допишите знаки доллара:
$A$2:$D$6. - Укажите номер столбца с ценой. В диапазоне A:D цена третья, значит 3.
- Последним аргументом напишите ЛОЖЬ, закройте скобку и нажмите Enter.
- Протяните формулу вниз за маркер в правом нижнем углу ячейки, чтобы она отработала для остальных артикулов.
Для «А-103» формула вернула 120, то есть цену маркера. Остаток подтягивается той же формулой, только с индексом 4: =ВПР(F2;$A$2:$D$6;4;ЛОЖЬ) даёт 85.
Кадры для статьи сделаны в таблице с британскими региональными настройками. Поэтому в строке формул стоят запятые, а ошибка подписана #N/A, а не #Н/Д. Названия функций при этом русские, как у любого пользователя с русским интерфейсом.
Канал основателя Checkroi Вани Буявца3 800 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединитьсяТочка с запятой или запятая
Разделитель аргументов в Google Таблицах зависит от региональных настроек файла. В русской справке Google формулы записаны через точку с запятой. В файле с американскими или британскими настройками Таблицы сами переписывают формулу через запятую.
Региональные настройки меняют в меню «Файл» → «Настройки», как описано в справке о региональных настройках. Там же сказано, что изменения применяются ко всему файлу и видны всем, кто с ним работает.
С названиями функций проще. Если набрать английское имя, Таблицы обычно сами переведут его на язык интерфейса:
=XLOOKUP(…)после ввода превращается в=ПРОСМОТРX(…);=VLOOKUP(…)считается так же, как=ВПР(…);- при включённом флажке «Всегда использовать названия функций на английском языке» в настройках таблицы все функции показываются по-английски.
Если формула скопирована из англоязычного урока и не работает, первым делом проверьте разделитель. Как переносить формулы между сервисами, подробно разобрано в статье о совместной работе Google Таблиц и Excel.
ВПР с другого листа и из другой таблицы
Другой лист того же файла
Прайс часто лежит на отдельном листе. Тогда перед диапазоном пишут имя листа и восклицательный знак:
=ВПР(A2;Прайс!$A$2:$D$100;3;ЛОЖЬ)Если в имени листа есть пробел, его берут в одинарные кавычки: 'Прайс ноябрь'!$A$2:$D$100. Проще всего не набирать имя руками, а во время ввода формулы перейти на нужный лист и выделить диапазон мышкой.
Другая Google Таблица
Из чужого файла данные достают функцией IMPORTRANGE, а ВПР оборачивают вокруг неё:
=ВПР(A2;IMPORTRANGE("ссылка на таблицу";"Прайс!A2:D100");3;ЛОЖЬ)В первом аргументе IMPORTRANGE стоит адрес таблицы-источника, во втором стоят лист и диапазон в кавычках. По справке Google по IMPORTRANGE, при первом обращении к файлу таблица запросит разрешение: нужно нажать «Разрешить доступ». После этого все редакторы вашей таблицы смогут импортировать данные из источника.
Если же нужно не искать по чужому файлу, а один раз перенести его к себе, удобнее вставить таблицу в Google Таблицу целиком.
Как убрать #Н/Д из результатов
Ошибка #Н/Д значит, что функция не нашла запрос в первом столбце. В нашем примере так вышло с артикулом «А-110», которого нет в прайсе.
Вместо ошибки можно вывести понятный текст. Для этого ВПР оборачивают в функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР(F3;$A$2:$D$6;4;ЛОЖЬ);"нет в прайсе")
У ЕСЛИОШИБКА есть минус: она прячет любую ошибку, включая опечатку в формуле. Если хотите ловить только «не нашлось», используйте IFNA с теми же аргументами. Знакомая по Excel функция ЕСНД в Google Таблицах вместо результата выдаёт #NAME?, так что пишите IFNA.
ВПР по двум условиям и сразу для всего столбца
Поиск по двум условиям
Сам по себе ВПР ищет по одному значению. Если у ручки два цвета и цены разные, артикула не хватит: нужен поиск по паре «товар + цвет».
Решение из справки Google: добавить слева вспомогательный столбец, который склеивает оба условия оператором &.
- В столбце A напишите
=B2&C2и протяните вниз. Получатся ключи вида «Ручкасиний», «Ручкакрасный». - В формуле поиска склейте условия так же:
=ВПР(F2&G2;$A$2:$D$5;4;ЛОЖЬ). - Для пары «Ручка» и «красный» формула вернёт 65, а синяя ручка с ценой 60 в ответ не попадёт.
Вспомогательный столбец можно потом скрыть, формулы от этого не сломаются.
Одна формула на весь столбец
Если артикулов сотни, протягивать формулу неудобно: любая новая строка останется без неё. В Google Таблицах ВПР можно обернуть в ARRAYFORMULA и передать ей весь столбец запросов:
=ARRAYFORMULA(ВПР(A13:A15;$B$2:$D$5;3;ЛОЖЬ))Формула стоит в одной ячейке, а ответы раскладываются вниз сами: для «Ручки» 60, для «Маркера» 120, для «Карандаша», которого нет в прайсе, #Н/Д. Оставьте ячейки под формулой пустыми: ответы займут их сами.
Чтобы в столбце запросов не появлялись артикулы с опечатками, их удобно выбирать из списка. Как его настроить, рассказали в инструкции про выпадающий список в Google Таблицах.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 800 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналЧастые ошибки ВПР и как их исправить
Все ситуации из таблицы воспроизводятся на том же прайсе. Большинство решается за минуту, если знать, где смотреть. Чаще всего ошибки приезжают вместе с данными, скопированными из Excel или выгрузки, поэтому после переноса таблицы из Excel сразу проверьте коды на пробелы.
| Что видите | Причина | Что сделать |
|---|---|---|
| #Н/Д, хотя артикул в прайсе есть | лишний пробел в конце: «А-103 » и «А-103» для функции разные | обернуть запрос в СЖПРОБЕЛЫ: =ВПР(СЖПРОБЕЛЫ(F2);…) |
| #Н/Д на одинаковых с виду кодах | в одном коде латинская A, в другом кириллическая А | заменить букву через «Найти и заменить» |
| #REF! | номер столбца больше ширины диапазона: в A:D четыре столбца, а указан 5 | уменьшить индекс или расширить диапазон |
| ответ из соседней строки | не указан ЛОЖЬ, и функция ищет приблизительно | дописать ЛОЖЬ четвёртым аргументом |
| после протягивания часть ячеек пустая или с ошибкой | диапазон без знаков $ съехал вниз | закрепить: $A$2:$D$6 или целые столбцы $A:$D |
| нашлось не то значение | в столбце поиска есть дубли, ВПР берёт первое совпадение | убрать дубли или искать по уникальному коду |
Приблизительный поиск подводит чаще остальных. Например, несуществующий артикул «А-1035» с ИСТИНА возвращает «Маркер»: строка «А-1035» по алфавиту стоит между «А-103» и «А-104». Справка Google прямо рекомендует выбирать ЛОЖЬ независимо от того, отсортирован ли столбец.
Но у ИСТИНА есть законное применение: шкалы, где нужно попасть в интервал. Допустим, скидка зависит от суммы заказа: от 0 ₽ скидки нет, от 5 000 ₽ она 5 %, от 10 000 ₽ уже 10 %. Пороги идут в первом столбце по возрастанию, и =ВПР(D8;$A$8:$B$10;2;ИСТИНА) для заказа на 7 300 ₽ вернёт 5, для 12 000 ₽ вернёт 10, для 3 000 ₽ вернёт 0. Функция берёт ближайший порог снизу, а точного совпадения здесь и не нужно.
Подстановочные знаки тоже работают только с ЛОЖЬ. Звёздочка заменяет любую последовательность символов, вопросительный знак заменяет один символ: =ВПР("*ер";$B$2:$D$6;2;ЛОЖЬ) находит первый товар, название которого заканчивается на «ер», и возвращает его цену, 120. Больше про формулы с условиями есть в обзоре основных функций Excel.
Чем заменить ВПР
У ВПР два ограничения: она ищет только в первом столбце и смотрит только вправо. Узнать по цене название товара, который стоит левее, она не умеет. Для таких задач есть две замены.
ПРОСМОТРX
Отдельно указываются столбец поиска и столбец ответа, поэтому номер считать не нужно:
=ПРОСМОТРX(F2;$A$2:$A$6;$C$2:$C$6)Для «А-103» формула вернула те же 120. По справке Google по XLOOKUP, четвёртым аргументом можно сразу задать значение на случай, если запрос не найден, и тогда ЕСЛИОШИБКА не нужна.
ИНДЕКС и ПОИСКПОЗ
Связка работает в обе стороны. ПОИСКПОЗ находит номер строки, ИНДЕКС достаёт значение из нужного столбца:
=ИНДЕКС($B$2:$B$6;ПОИСКПОЗ(120;$C$2:$C$6;0))Формула нашла цену 120 в столбце C и вернула «Маркер» из столбца B, левее. ВПР так не может. Если данные лежат не в столбцах, а в строках, берите горизонтальную сестру ВПР, функцию ГПР.
Поиск по Google Таблице без формул
Бывает, что формула не нужна, а нужно просто найти слово на листе. Для этого в Таблицах два инструмента.
Быстрый поиск Ctrl+F
Нажмите Ctrl+F (на Mac ⌘+F) и введите слово. Найденные ячейки подсвечиваются, а в поле поиска видно, сколько совпадений нашлось и на каком из них вы сейчас. Стрелками переходят к следующему.
Окно «Найти и заменить»
Окно открывается сочетанием Ctrl+H (на Mac ⌘+Shift+H), сочетания перечислены в справке о быстрых клавишах Таблиц. Здесь больше настроек:
- в выпадающем списке под полями выбирают, где искать: на всех листах или только на части таблицы;
- «Учитывать регистр» отличает «маркер» от «Маркер»;
- «Совпадение с полным содержанием ячейки» не найдёт «А-103» внутри «А-1035»;
- «Поиск по формулам» ищет в тексте формул, а не только в результатах;
- «Поиск с использованием регулярных выражений» ищет по шаблону.
Через это окно удобно чинить данные перед ВПР: заменить латинскую A на кириллическую во всём столбце или убрать лишние символы. Полное описание параметров есть в справке Google о поиске и замене, а как то же самое делается в Excel, рассказываем в статье о поиске в Excel.
Где освоить Google Таблицы системно
ВПР закрывает одну задачу: подтянуть значение по ключу. Когда таблиц становится много, нужны ещё QUERY, сводные таблицы, IMPORTRANGE между отделами и аккуратная структура данных, иначе формулы ломаются при каждой правке.
Всему этому учат на курсах по Google Sheets. Цены и отзывы удобно сравнить в каталоге курсов по Google Таблицам.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel и Google-таблицы Перейти на сайт курса |  Эдюсон | 39 900 ₽ | 3325 ₽/мес. | 1 месяц | Обзор курса |
| Excel и Google-таблицы для бухгалтера Перейти на сайт курса | Эдюсон | 29 000 ₽ | 2416 ₽/мес. | 1 месяц | Обзор курса |
| Основы Google Sheets Перейти на сайт курса |  ProductStar | 14 990 ₽ | 1499 ₽/мес. | 1 месяц | Обзор курса |
| Excel и Google-таблицы для бухгалтеров Перейти на сайт курса |  Бруноям | 16 900 ₽ | 1408 ₽/мес. | 1 месяц | Обзор курса |
Больше программ — в полном каталоге курсов по Google Sheets
