Поиск по строкам с помощью HLOOKUP

HLOOKUP — горизонтальный аналог VLOOKUP. Буква H означает «горизонтальный»: вместо поиска вниз по столбцу эта функция ищет в верхней строке таблицы, а затем возвращает значение из той же колонки, но расположенной ниже.

Используйте HLOOKUP, когда данные расположены горизонтально: метки находятся в строке, а не в столбце.

HLOOKUP устроен так же, как VLOOKUP, но с одним важным отличием. Его аргументы:

• искомое значение — что нужно найти в верхней строке
• табличный массив — диапазон ячеек
• номер строки — строка, из которой нужно вернуть значение (отсчёт ведётся сверху)
• [поиск по диапазону] — TRUE для приближённого совпадения, FALSE для точного

Обратите внимание: третий аргумент теперь задаёт номер строки, а не столбца, потому что данные расположены вертикально под верхней строкой.

Представьте квартальную таблицу, в которой месяцы расположены в строке 1 в ячейках диапазона A1:D3:

• Строка 1: Янв., Февр., Март, Апр.
• Строка 2 (Продажи): 100, 150, 120, 200
• Строка 3 (Затраты): 60, 70, 65, 90

Верхняя строка с месяцами — это строка, в которой HLOOKUP выполняет поиск. В расположенных ниже строках находятся данные, которые нужно вернуть.

Чтобы найти показатель продаж за март, HLOOKUP ищет «март» в верхней строке, а затем возвращает значение из строки 2 (продажи):

Если прочитать это вслух: найдите "Mar" в верхней строке диапазона A1:D3 и верните значение из 2-й строки, используя точное совпадение. Результат — 120.

Номер строки отсчитывается от верхней границы табличного массива, а не от строки 1 листа.

В нашем диапазоне A1:D3 строки пронумерованы так:

• Строка 1 = месяцы (строка поиска)
• Строка 2 = Продажи
• Строка 3 = Затраты

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

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

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

Обе функции работают по одному принципу, но повернуты на 90 градусов:

• VLOOKUP: ищет вниз по первому столбцу, третий аргумент задаёт номер столбца
• HLOOKUP: ищет по верхней строке, третий аргумент задаёт номер строки

Выбирайте функцию в зависимости от расположения данных. Если заголовки идут вертикально сбоку, используйте VLOOKUP. Если они расположены горизонтально сверху, используйте HLOOKUP.

Аргумент поиска по диапазону работает так же, как в VLOOKUP:

• FALSE = точное совпадение; верхнюю строку не нужно сортировать, для отсутствующих значений возвращается #N/A
• TRUE = приближённое совпадение; верхняя строка должна быть отсортирована слева направо по возрастанию

Для поиска по меткам, например названиям месяцев, используйте FALSE. Для строки пороговых значений, разбитых на диапазоны, подойдёт TRUE, но уровни редко располагают горизонтально.

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

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

Горизонтальные таблицы встречаются чаще, чем можно ожидать:

• Финансовые модели, где годы или месяцы указаны в столбцах
• Таблицы результатов, где раунды расположены сверху
• Импортированные отчёты, в которых периоды указаны в строке заголовков

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

Как и в случае с VLOOKUP, жёстко заданный индекс строки может быть ненадёжным. Если вставить строку, индекс перестанет соответствовать данным. Можно сделать его динамическим, найдя положение строки с помощью MATCH.

Здесь MATCH определяет, в какой строке находится метка «Расходы» среди меток в столбце A, и передаёт это число функции HLOOKUP. Теперь формула продолжает работать, даже если строки меняются местами. Это первое знакомство с комбинированием функций поиска, которое подробнее рассматривается далее.

Проверьте, насколько хорошо Вы освоили горизонтальный поиск.

Теперь Вы умеете обращаться к данным в горизонтальных таблицах:

• HLOOKUP выполняет поиск по верхней строке и возвращает значение из строки ниже
• Третий аргумент — это номер_индекса_строки, отсчитываемый от верхней границы диапазона
• FALSE и TRUE работают так же, как в VLOOKUP, для точного и приблизительного совпадения
• При копировании вправо зафиксируйте таблицу с помощью $
• Используйте эту функцию, когда заголовки расположены горизонтально, а не вертикально

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