Функция VLOOKUP (рус. — ВПР) входит в топ-3 самых популярных в Excel — после SUM и AVERAGE. Ее задача — найти нужное значение в таблице данных и вывести его в заданную ячейку.
VLOOKUP ищет значение по вертикали, то есть среди строчек (на это указывает первая буква V — Vertical). Кроме того, в Excel есть функция HLOOKUP, которая делает горизонтальный поиск, по столбцам. Но нашему глазу понятнее, когда данные представлены несколькими заглавными столбцами, а основная информация записана построчно, поэтому VLOOKUP — более популярна. Рассмотрим эту функцию детальнее.
*В статье используются формулы из английской версии MS Excel.
Зачем нужна функция VLOOKUP
Синтаксис функции VLOOKUP одинаковый как в Excel, так и в Google Spreadsheets. Он выглядит так:
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
= VLOOKUP (искомое значение, массив таблицы, номер столбца, [интервальный просмотр]), где
- искомое значение (что ищем?) — искомое значение или ссылка на ячейку с искомым значением
- массив таблицы (где ищем?) — ссылка на диапазон ячеек, в первом столбце которого будет поиск искомого значения
- номер столбца — номер столбца в диапазоне, из которого будет возвращено значение
- [интервальный просмотр] — не обязательный, но важный параметр, который отвечает на вопрос «отсортирован ли по возрастанию первый столбец диапазона поиска?». Если отсортирован — мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. Если этот параметр опущен — он по умолчанию равен 1.
Рассмотрим пример. У нас есть таблица с тремя столбцами — артикул, наименование товара и его стоимость.
Допустим, нам нужно найти стоимость товара с артикулом 1101. На скрине интуитивно понятно, что она равна 58, но когда в таблице тысячи строк — поиск усложняется. С функцией VLOOKUP разобраться будет легче.
Чтобы найти стоимость артикула 1101 — в ячейке, куда необходимо вывести значение, прописываем формулу
= VLOOKUP
- <что ищем?> — искомое значение может быть прописано как ссылка на ячейку (в нашем случае ячейка F2) либо как числовое значение 1101 (если нужно найти текстовое значение — его выделяем кавычками “”)
- <где ищем?> — прописываем диапазон таблицы: он может быть записан как A2:C7 либо как именованный диапазон
- <номер столбца> — в нашем случае стоимость товара прописана в третьем по счету столбце
*Результат формулы, записанной тремя разными способами, будет одинаковым.
В этом примере 4-й параметр — [интервальный просмотр] — пропущен, и функция по умолчанию решила, что наша таблица отсортирована по возрастанию номера артикула. В примере выше это оказалось правдой — потому мы получили значение, которое ожидали. Но дальше рассмотрим пример, где подобное бы не сработало.
Как устроена логика VLOOKUP: функция начнет поиск искомого значения с первой строки и будет продолжать его построчно. Она завершит поиск, как только найдет значение большее, чем мы запрашивали, — и вернет предыдущее.
Например, нам нужно найти стоимость артикула 1104 (на скрине ниже он находится на 7-й строке). Функция начнет искать построчно, найдет артикул 1105 (то есть больший, чем искомый) и завершит поиск, вернув нам значение предыдущего артикула — 1103.
То есть мы указали, что столбец отсортирован — значит, артикул 1104 не может находиться ниже, чем 1105.
Еще один важный момент: если нужно найти стоимость, к примеру, артикула 1108, то функция вернет последнюю строку диапазона — не указав, что артикула 1108 в этой таблице вообще не существует.
Если вы отметите, что 4-й параметр не обязательный, это ускорит работу. Когда таблица содержит тысячи строк, отсортированных по возрастанию, функции нет смысла проходить по всему набору данных. Ей достаточно остановиться на первом найденном искомом значении. Поэтому, если вы уверенны в корректной сортировке своих данных, то 4-й параметр можно упустить, в противном случае — укажите FALSE либо 0.
Сортировка по возрастанию относится не только к числовым, но и к текстовым выражениям — функция может делать поиск в алфавитном порядке.
Хотите получать дайджест статей?
Кроме того, VLOOKUP допускает неточный поиск значения — вы можете опустить какой-то из символов, если он неизвестен. Для этого используйте символы подстановки:
- ? — заменяет один символ в строке
- * — заменяет любое количество символов
Например, нужно найти продукт, в последней букве которого мы не уверены. Для этого подставим знак «?» — «BA?» — и функция вернет нам стоимость продукта BAC:
Если же нужно найти именно вопросительный знак или звездочку — введите значок тильды (~) перед искомым символом.
Кроме того, функцию VLOOKUP можно использовать для объединения двух таблиц, у которых есть один общий ключ — в нашем случае это артикул.
Синтаксис функции остается таким же: ищем артикул из первой таблицы в заданном диапазоне другой таблицы. При этом указываем, в каком столбце находится значение, которое нужно отобразить в первой таблице.
На скриншоте выше артикул 1106 вернулся с ошибкой #N/A — это означает, что поиск по номеру не дал результата, поскольку артикула с таким номером нет во второй таблице. Чтобы не выводить в ячейки значение #N/A, можно доработать формулу функцией IFERROR:
Недостатки функции VLOOKUP
Главный минус в том, что поиск искомого значения может проходить только по первому столбцу заданного диапазона, а возвратить нужное значение функция может только из столбцов правее.
В примерах выше этот недостаток не проявлялся, поскольку искомое значение (артикул) было в первом столбце диапазона, а остальные значения находились справа от него. Но если столбец с искомыми значениями находится в середине таблицы, то вывести значение мы можем только из столбцов правее. Например:
В таком случае мы сможем вывести только стоимость наименования, поскольку этот столбец (С) находится правее от столбца с названием (B). Код (A) находится левее, поэтому функция VLOOKUP в подобном случае бессильна.
Второй недостаток VLOOKUP заключается в том, что функция перестает работать, если убрать или добавить столбец в таблицу поиска. Вставленный или удаленный элемент изменит результат формулы, поскольку синтаксис функции требует указывать весь диапазон и конкретный номер столбца, из которого нужно извлечь данные.
Кроме того, функция VLOOKUP имеет ограничение по длине искомого значения в 255 символов, иначе будет возвращена ошибка #VALUE! (#ЗНАЧ!).
Какие есть аналоги VLOOKUP
Чтобы обойти ограничение по поиску в первом столбце диапазона, в Excel появилась функция XLOOKUP. Она доступна только для пользователей Microsoft 365 и Excel 2021.
Синтаксис функции выглядит так:
=XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP (искомое значение, просматриваемый массив, возвращаемый массив, [если не найдено], [тип сопоставления], [режим поиска]), где
- искомое значение (что ищем?) — искомое значение или ссылка на ячейку, содержащую искомое значение
- просматриваемый массив (где ищем?) — массив или диапазон, в котором вы ищете искомое значение
- возвращаемый массив — массив или диапазон, из которого вы хотите получить значение
- [если не найдено] (не обязательно) — возвращаемое значение, если совпадение не найдено
- [тип сопоставления] (не обязательно) — как сопоставлять искомое значение со значениями в искомом массиве:
- 0 (по умолчанию) = точное совпадение. Если совпадений не найдено, вернуть #N/A
- -1 = точное совпадение. Если совпадений не найдено, вернуть следующее меньшее значение
- 1 = точное совпадение. Если совпадений не найдено, вернуть следующее большее значение
- 2 = частичное совпадение
- [режим поиска] (не обязательно) — порядок поиска:
- 1 (по умолчанию) = искать искомое значение от первого до последнего элемента в искомом массиве
- -1 = искать искомое значение от последнего до первого элемента
Таким образом, чтобы получить значение (артикул), которое находится слева от столбца с искомыми значениями, можно использовать следующую функцию:
В Excel предыдущих версий эту задачу можно решить, используя комбинацию из функций INDEX+MATCH. Синтаксис выглядит так:
= INDEX (column to return a value from, MATCH (lookup value, column to look up against, [match_mode]) [column number])
= INDEX (возвращаемый массив, MATCH (искомое значение, просматриваемый массив, [тип сопоставления]), [номер столбца]), где
- возвращаемый массив — столбец, из которого нужно извлечь данные
- искомое значение (что ищем?)
- просматриваемый массив (где ищем?)
- [тип сопоставления] (не обязательно) — для точного совпадения следует указать 0
- [номер столбца] (не обязательно)
Функция вернет такие же значения, как и XLOOKUP:
Таким образом, связка функций INDEX и MATCH фактически полностью заменяет функцию VLOOKUP. Но в то же время у такой комбинации нет недостатков VLOOKUP, которые вытекают из синтаксиса самой функции, и этот вариант доступен для пользователей ранних версий Excel.
Весь бизнес-контент в удобном формате. Интервью, кейсы, лайфхаки корп. мира — в нашем телеграм-канале. Присоединяйтесь!
Хотите получать дайджест статей?
