Функция ПРОСМОТРX / XLOOKUP в Excel и аналог в Google Таблицах

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

Давайте посмотрим на нее, а также на то, как ее можно заменить в Google Таблицах.

Итак, если возвращаемый столбец левее искомых значений, то ВПР работать не будет:

Такая функция ВПР выдаст ошибку #Н/Д - ведь она может вести поиск только по первому столбцу таблицы, а нам нужно искать в B:B, а тянуть данные из A:A

Обычно эта проблема решалась сочетанием функций ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX.

А теперь можно решить все одной - ПРОСМОТРX.

ПРОСМОТРX в Excel

И с ней все просто! Есть три обязательных аргумента — искомое значение, просматриваемый массив, возвращаемый массив. Что ищем, где ищем и что нужно получить.

В нашем случае будет выглядеть вот так:

=ПРОСМОТРX(F3;B:B;A:A)

У нового просмотра есть и другие аргументы, необязательные. Например, "встроенная ЕСЛИОШИБКА", то есть можно сразу указать, что будет отображаться вместо Н/Д, если ничего не было найдено. Это четвертый аргумент, называется если_ничего_не_найдено.

Плюс есть тип соответствия — как раньше в ВПР, можно искать точное соответствие или ближайшее число, но теперь можно также использовать подстановочные символы * и ?, как в функциях подсчета и суммирования типа СУММЕСЛИ. Для этого нужно указать тип соответствия в ПРОСМОТРX = 2.

Последний аргумент - тип поиска. Теперь можно искать и снизу вверх (-1), и сверху вниз как раньше (1 или пропущено).

Левый ВПР в Google Таблицах

Ну а как мы ответим на это, будучи фанатами Google Таблиц? Как мы когда-то писали, там это решается с помощью массива.

Мы виртуально "пересобираем" исходную таблицу в правильном порядке в массив внутри обычной функции ВПР / VLOOKUP.
Напомним, что для горизонтального объединения массивов их нужно взять в фигурные скобки и поставить между ними обратную косую черту (либо запятую, для других региональных настроек):

=ВПР(A2;{'Прайс-лист'!B:B \ 'Прайс-лист'!A:A};2;0)

А еще с помощью этой конструкции можно создать массив только из двух столбцов (столбца поиска и столбца, который будет возвращаться) и не использовать внутри ВПР таблицу из двадцати столбцов, что может работать медленно.