Поиск…
Вступление
Ищет значение в первом столбце массива таблицы и возвращает значение в той же строке из другого столбца в массиве таблицы.
V в VLOOKUP соответствует вертикали. Используйте VLOOKUP вместо HLOOKUP, когда ваши значения сравнения находятся в столбце слева от данных, которые вы хотите найти.
Синтаксис
- VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
параметры
| параметр | Описание |
|---|---|
| искомое_значение | Значение, которое вы ищете в левом столбце таблицы. Может быть либо фиксированное значение, ссылка на ячейку или именованный диапазон ( обязательно ) |
| таблица_массив | Диапазон ячеек, состоящий из столбца, который вы хотите искать в левой части, со значениями в ячейках справа, которые вы хотите вернуть. Может быть ссылкой на ячейку Excel или именованным диапазоном. ( обязательно ) |
| номер_столбца | Номер столбца, из которого вы хотите вернуть данные, считая от самого левого столбца в таблице ( обязательно ) |
| range_lookup | Контролирует способ поиска. Если FALSE или 0, Excel выполнит точный поиск и вернется только там, где есть точное совпадение в левой колонке. Внимание к точности с округлением очень важно для этого поиска с числовыми значениями. Если TRUE или 1, Excel выполнит и приблизит поиск и вернет последнее значение, равное или превышающее. Таким образом, первый столбец должен быть отсортирован в порядке возрастания и приближенного поиска. range_lookup . ( Необязательно - по умолчанию TRUE) |
замечания
Аналогичные функции:
- HLOOKUP (то же, что и VLOOKUP, но выполняет поиск по горизонтали, а не по вертикали)
- MATCH (если ваш lookup_value имеет совпадение, возвращает номер строки в пределах диапазона)
- LOOKUP (аналогично VLOOKUP и MATCH и предоставляется для обратной совместимости)
Общие ошибки:
- Не задавая параметр range_lookup и получая поведение по умолчанию, неточное соответствие
- Не фиксировать и абсолютный диапазон адресов в table_array - при копировании формулы ссылка на справочную таблицу также перемещается
Используя VLOOKUP, чтобы получить фамилию человека из своего идентификатора сотрудника
Vlookup находит некоторое значение в крайнем левом столбце диапазона и возвращает значение некоторого количества столбцов вправо и в той же строке.
Предположим, вы хотите найти фамилию Employee ID 2 из этой таблицы:
=VLOOKUP(2,$A$2:$C$4,3,0)
- Значение, которое вы получаете для данных, равно 2
- Таблица, которую вы ищете, находится в диапазоне $ A $ 2: $ C $ 4
- Столбец, из которого вы хотите вернуть данные, - это 3- й столбец слева
- Вы хотите только вернуть результаты, когда есть точное соответствие ( 0 )
Обратите внимание: если нет точного соответствия идентификатора сотрудника, VLOOKUP вернет #N/A
Использование VLOOKUP для выработки бонусных процентов (пример с поведением «по умолчанию»)
В большинстве случаев range_lookup используется как FALSE (точное совпадение). Значение по умолчанию для этого параметра TRUE - оно менее часто используется в этой форме, но в этом примере показана одна функция usecase. Супермаркет дает бонус на основе ежемесячных расходов клиентов.
Если клиент ежемесячно тратит 250 евро и более, он получает 1% бонуса; 500 евро и более дает 2,5% и т. Д. Конечно, клиент не всегда будет тратить точно одно из значений в таблице!
=VLOOKUP(261,$A$2:$B$6,2,TRUE)
В этом режиме VLOOKUP найдет первое значение в столбце A (следующие шаги снизу вверх), меньшее или равное значению 261 -> т.е. мы получим возвращаемое значение 1% . Для этого неточного соответствия таблица должна быть отсортирована в порядке возрастания первого столбца.
- Значение, на которое вы извлекаете данные, составляет 261
- Таблица, которую вы ищете, находится в диапазоне $ A $ 2: $ B $ 6
- Столбец, из которого вы хотите вернуть данные, - это 2- й столбец слева
- Вы хотите только вернуть результаты, когда существует неточное соответствие ( TRUE ), мы могли бы оставить это значение ИСТИНА, поскольку оно по умолчанию
Использование VLOOKUP с приблизительным соответствием.
Когда параметр range_lookup либо опущен, либо TRUE, либо 1, VLOOKUP найдет приблизительное совпадение. Под «приблизительным» подразумевается, что VLOOKUP будет соответствовать наименьшему значению, которое больше вашего lookup_value . Обратите внимание, что ваш table_array должен быть отсортирован в порядке возрастания по значениям поиска. Результаты будут непредсказуемыми, если ваши значения не отсортированы.
Использование VLOOKUP с точным соответствием
Основная идея VLOOKUP - искать информацию в таблице электронных таблиц и размещать ее в другой.
Например, предположим, что это таблица в Sheet1:
John 12/25/1990
Jane 1/1/2000
В Sheet2 поместите John , Andy и Jane в A1, A2 и A3.
В B1, справа от John , я разместил:
=VLOOKUP(A1,Sheet1!$A$1:$B$4,2,FALSE)
Вот краткое описание параметров, предоставленных VLOOKUP. А1 означает, что я ищу John в A1 листа Sheet2.
Sheet1!$A$1:$B$4
говорит функции смотреть на Sheet1, столбцы от A до B (и строки с 1 по 4). Знаки доллара необходимы, чтобы заставить Excel использовать абсолютные (а не относительные) ссылки. (Относительные ссылки заставили бы все это смещаться нежелательным образом при копировании формулы вниз.
2 означает возврат второго столбца, который является датой.
FALSE означает, что вам требуется точное совпадение.
Затем я скопировал B1 на B2 и B3. (Самый простой способ сделать это - щелкнуть по B1, чтобы выделить его. Затем удерживайте клавишу shift и дважды нажмите стрелку вниз. Теперь выделены B1, B2 и B3. Затем нажмите Ctrl-D, чтобы заполнить формулу Если все сделано правильно, нужно иметь ту же формулу в B3, что и в B1. Если формулы для таблицы поиска изменяются , например, из Sheet1!A1:B3 в Sheet1:A3:B5 , тогда вы должны использовать абсолютные ссылки (с знак доллара), чтобы предотвратить изменение.)
Вот результаты:
John 12/25/1990
Andy #N/A
Jane 1/1/2000
Он нашел Джона и Джейн и вернул свои даты рождения. Он не нашел Энди, и поэтому он отображает #N/A