Функция ГПР: тот же принцип, но по горизонтали Теория

ГПР: поиск по ключу в первой строке

ГПР ищет ключ по первой строке справочника и возвращает поле из указанной строки того же столбца.

Иногда данные справочника расположены горизонтально: идентификаторы — в первой строке, связанные данные — в строках ниже. Именно для таких таблиц существует ГПР — функция горизонтального поиска (в английском Excel — HLOOKUP, Horizontal Lookup).

Чем ГПР отличается от ВПР

ВПР ГПР
Направление поиска Сверху вниз по первому столбцу Слева направо по первой строке
Результат Значение из указанного столбца найденной строки Значение из указанной строки найденного столбца
Когда использовать Справочник с вертикальной структурой Справочник с горизонтальной структурой

Синтаксис

=ГПР(искомое_значение; таблица; номер_строки; [интервальный_просмотр])
АргументЧто это
искомое_значение Ключ, который ищем в первой строке диапазона
таблица Диапазон справочника; ключи — в первой строке
номер_строки Порядковый номер строки внутри диапазона, из которой берём результат
интервальный_просмотр ЛОЖЬ (0) — точное совпадение; ИСТИНА (1) или пропуск — приближённое. Для идентификаторов укажите ЛОЖЬ. Квадратные скобки обозначают необязательность аргумента и в формулу не вводятся

Пример: горизонтальный прайс-лист

Прайс-лист организован горизонтально: в строке 1 — коды товаров, в строке 2 — условные цены, в строке 3 — скидки:

ABCD
1Код товараT-100T-200T-300
2Цена5001200800
3Скидка20%20%10%

Нужно найти цену товара T-200 (код в ячейке F2):

=ГПР(F2; $A$1:$D$3; 2; ЛОЖЬ)

Функция ищет «T-200» в первой строке диапазона, находит его в столбце C и возвращает значение из второй строки того же столбца — цену 1200.

Нумерация строк

Как и в ВПР с номером столбца, номер строки в ГПР отсчитывается внутри диапазона, а не по листу. Если диапазон $A$1:$D$3:

  • Строка 1 внутри диапазона = строка 1 листа (коды товаров)
  • Строка 2 внутри диапазона = строка 2 листа (цены)
  • Строка 3 внутри диапазона = строка 3 листа (скидки)

Если диапазон начинается не с первой строки листа, нумерация всё равно ведётся с 1 внутри диапазона. Например, для $B$5:$F$8 номер 3 означает строку 7 листа. ГПР может вернуть и первую строку диапазона при номере 1; при номере больше числа строк возникает #ССЫЛКА!.

Поведение при отсутствии совпадения

Если искомый ключ не найден в первой строке при точном поиске (ЛОЖЬ), ГПР возвращает ту же ошибку, что и ВПР — #Н/Д. ЕСЛИОШИБКА может заменить её сообщением, но перехватывает и другие ошибки: перед применением проверьте диапазон и номер строки.

Режим поиска: при ИСТИНА или пропуске четвёртого аргумента отсортируйте ключи первой строки слева направо по возрастанию. Функция выбирает точный ключ или наибольший ключ, не превышающий искомый, и возвращает поле того же столбца; если искомое значение меньше минимального ключа, результат — #Н/Д. При ЛОЖЬ сортировка не нужна, регистр букв не учитывается. Знаки * и ? в текстовом ключе работают как подстановочные; для поиска самих символов используйте ~. Для однозначного поиска поддерживайте уникальность ключей справочника.