Lookup функция в эксель - Учим Эксель

LOOKUP function

Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column.

For example, let’s say you know the part number for an auto part, but you don’t know the price. You can use the LOOKUP function to return the price in cell H2 when you enter the auto part number in cell H1.

An example of how you might use the LOOKUP function

Use the LOOKUP function to search one row or one column. In the above example, we’re searching prices in column D.

Tips: Consider one of the newer lookup functions, depending on which version of Office you are using.

Use VLOOKUP to search one row or column, or to search multiple rows and columns (like a table). It’s a much improved version of LOOKUP. Watch this video about how to use VLOOKUP.

If you are using Microsoft 365, use XLOOKUP — it’s not only faster, it also lets you search in any direction (up, down, left, right).

There are two ways to use LOOKUP: Vector form and Array form

Vector form: Use this form of LOOKUP to search one row or one column for a value. Use the vector form when you want to specify the range that contains the values that you want to match. For example, if you want to search for a value in column A, down to row 6.

Example of a vector

Array form: We strongly recommend using VLOOKUP or HLOOKUP instead of the array form. Watch this video about using VLOOKUP. The array form is provided for compatibility with other spreadsheet programs, but it’s functionality is limited.

An array is a collection of values in rows and columns (like a table) that you want to search. For example, if you want to search columns A and B, down to row 6. LOOKUP will return the nearest match. To use the array form, your data must be sorted.

Example of a table, which is an array

Vector form

The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.

Syntax

LOOKUP(lookup_value, lookup_vector, [result_vector])

The LOOKUP function vector form syntax has the following arguments:

lookup_value Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

lookup_vector Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

Important: The values in lookup_vector must be placed in ascending order: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

result_vector Optional. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector. It has to be the same size.

Remarks

If the LOOKUP function can’t find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.

Vector examples

You can try out these examples in your own Excel worksheet to learn how the LOOKUP function works. In the first example, you’re going to end up with a spreadsheet that looks similar to this one:

An example of using the LOOKUP function

Copy the data in following table, and paste it into a new Excel worksheet.

Copy this data into column A

Copy this data into column B

Next, copy the LOOKUP formulas from the following table into column D of your worksheet.

Copy this formula into the D column

Here’s what this formula does

Here’s the result you’ll see

=LOOKUP(4.19, A2:A6, B2:B6)

Looks up 4.19 in column A, and returns the value from column B that is in the same row.

=LOOKUP(5.75, A2:A6, B2:B6)

Looks up 5.75 in column A, matches the nearest smaller value (5.17), and returns the value from column B that is in the same row.

Интересно почитать:  Таблица функция в excel

=LOOKUP(7.66, A2:A6, B2:B6)

Looks up 7.66 in column A, matches the nearest smaller value (6.39), and returns the value from column B that is in the same row.

=LOOKUP(0, A2:A6, B2:B6)

Looks up 0 in column A, and returns an error because 0 is less than the smallest value (4.14) in column A.

For these formulas to show results, you may need to select them in your Excel worksheet, press F2, and then press Enter. If you need to, adjust the column widths to see all the data.

Array form

Tip: We strongly recommend using VLOOKUP or HLOOKUP instead of the array form. See this video about VLOOKUP; it provides examples. The array form of LOOKUP is provided for compatibility with other spreadsheet programs, but its functionality is limited.

The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values that you want to match are in the first row or column of the array.

Syntax

The LOOKUP function array form syntax has these arguments:

lookup_value Required. A value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.

If LOOKUP can’t find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

If the value of lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

array Required. A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.

The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.

If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for the value of lookup_value in the first row.

If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.

With the HLOOKUP and VLOOKUP functions, you can index down or across, but LOOKUP always selects the last value in the row or column.

Important: The values in array must be placed in ascending order: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

30 функций Excel за 30 дней: ПРОСМОТР (LOOKUP)

Вчера в марафоне 30 функций Excel за 30 дней мы забавлялись с функцией REPT (ПОВТОР), создавая диаграммы снутри ячейки и используя ее для обычного подсчета. Сейчас пн, и нам в очередной раз пора надеть свои шапки мыслителей.

В 16-й денек марафона мы займёмся исследованием функции LOOKUP (ПРОСМОТР). Это близкий друг VLOOKUP (ВПР) и HLOOKUP (ГПР), но работает она мало по-другому.

Итак, давайте изучим теорию и испытаем на практике функцию LOOKUP (ПРОСМОТР). Если у Вас есть доборная информация либо примеры по использованию данной для нас функции, пожалуйста, делитесь ими в комментах.

Функция 16: LOOKUP (ПРОСМОТР)

Функция LOOKUP (ПРОСМОТР) возвращает значение из одной строчки, 1-го столбца либо из массива.

Функция ПРОСМОТР в Excel

Как можно применять функцию LOOKUP (ПРОСМОТР)?

Функция LOOKUP (ПРОСМОТР) возвращает итог, в зависимости от искомого значения. С ее помощью Вы можете:

  • Отыскать крайнее значение в столбце.
  • Отыскать крайний месяц с отрицательным показателем продаж.
  • Преобразовать успеваемость учащихся из процентов в буквенную систему оценок.

Синтаксис LOOKUP (ПРОСМОТР)

Функция LOOKUP (ПРОСМОТР) имеет две синтаксические формы – векторную и массива. В векторной форме функция отыскивает значение в данном столбце либо строке, а в форме массива – в первой строке либо столбце массива.

Интересно почитать:  Функция мин в excel

Векторная форма имеет последующий синтаксис:

  • lookup_value (искомое_значение) – быть может текстом, числом, логическим значением, именованием либо ссылкой.
  • lookup_vector (просматриваемый_вектор) – спектр, состоящий из одной строчки либо 1-го столбца.
  • result_vector (вектор_результатов) – спектр, состоящий из одной строчки либо 1-го столбца.
  • спектры аргументов lookup_vector (просматриваемый_вектор) и result_vector (вектор_результатов) должны быть 1-го размера.

Форма массива имеет вот таковой синтаксис:

  • lookup_value (искомое_значение) – быть может текстом, числом, логическим значением, именованием либо ссылкой.
  • поиск производится в согласовании с размерностью массива:
    • если в массиве больше столбцов, чем строк, то поиск происходит в первой строке;
    • если количество строк и столбцов идиентично либо строк больше, то поиск происходит в первом столбце.

    Ловушки LOOKUP (ПРОСМОТР)

    • В функции LOOKUP (ПРОСМОТР) нет функции для поиска четкого совпадения, которая есть в VLOOKUP (ВПР) и в HLOOKUP (ГПР). Если разыскиваемое значения отсутствует, то функция вернет наибольшее значение, не превышающее разыскиваемое.
    • Массив либо вектор, в котором производится поиск, должен быть отсортирован по возрастанию, по другому функция может возвратить неверный итог.
    • Если 1-ое значение в просматриваемом массиве/векторе больше, чем разыскиваемое значение, то функция выдаст сообщение о ошибке #N/A (#Н/Д).

    Пример 1: Находим крайнее значение в столбце

    В форме массива функция LOOKUP (ПРОСМОТР) быть может применена для поиска крайнего значения в столбце.

    Справка Excel приводит значение 9,99999999999999E+307 как наибольшее число, которое быть может записано в ячейке. В нашей формуле оно будет задано, как разыскиваемое значение. Предполагается, что такое огромное число найдено не будет, потому функция вернет крайнее значение в столбце D.

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

    Функция ПРОСМОТР в Excel

    Пример 2: Находим крайний месяц с отрицательным значением

    В этом примере мы будем применять векторную форму LOOKUP (ПРОСМОТР). В столбце D записаны значения продаж, а в столбце E – наименования месяцев. В некие месяцы дела шли не весьма отлично, и в ячейках со значениями продаж возникли отрицательные числа.

    Чтоб отыскать крайний месяц с отрицательным числом, формула с LOOKUP (ПРОСМОТР) будет инспектировать для всякого значения продаж справедливость утверждения, что оно меньше 0 (неравенство в формуле). Дальше мы делим 1 на приобретенный итог, в итоге имеем или 1, или сообщение о ошибке #DIV/0 (#ДЕЛ/0).

    Так как разыскиваемое значение равное 2 найдено не будет, то функция изберет последнюю найденную 1, и вернет соответственное значение из столбца E.

    Функция ПРОСМОТР в Excel

    Пояснение: В данной формуле заместо аргумента lookup_vector (просматриваемый_вектор) подставлено выражение 1/(D2:D8<0), которое образует в оперативки компа массив, состоящий из 1 и значений ошибки #DIV/0 (#ДЕЛ/0). 1 гласит о том, что в соответственной ячейке спектра D2:D8 находится значение меньше 0, а ошибка #DIV/0 (#ДЕЛ/0) – что больше либо равное 0. В итоге наша задачка сводится к тому, чтоб отыскать последнюю 1 в сделанном виртуальном массиве, и на основании этого возвратить заглавие месяца из спектра E2:E8.

    Пример 3: Преобразовываем успеваемость учащихся из процентов в буквенную систему оценок

    Ранее мы уже решали похожую задачку при помощи функции VLOOKUP (ВПР). Сейчас воспользуемся функцией LOOKUP (ПРОСМОТР) в векторной форме, чтоб конвертировать успеваемость учащихся из процентов в буквенную систему оценок. В отличие от VLOOKUP (ВПР) для функции LOOKUP (ПРОСМОТР) не принципиально, чтоб проценты находились в первом столбце таблицы. Вы сможете избрать полностью хоть какой столбец.

    В последующем примере баллы указаны в столбце D, они отсортированы в порядке возрастания, а надлежащие им буковкы – в столбце C, слева от столбца, по которому делается поиск.

    Как извлечь данные из электрической таблицы, используя VLOOKUP, MATCH и INDEX

    Когда для вас необходимо отыскать и извлечь столбец данных из одной таблицы и поместить ее в другую, используйте функцию VLOOKUP. Эта функция работает в хоть какой версии Excel под Windows и на Mac, также в Гугл Sheets. Она дозволит для вас отыскать данные в одной таблице используя некий идентификатор в ней, общий с иной таблицей. Обе таблицы могут находиться на различных листах либо даже в в различных книжках. Есть также функция HLOOKUP, которая делает то же самое, но с данными, расположенными горизонтально, по строчкам.

    Функции MATCH и INDEX полезны, когда вас интересует положение определенных данных, таковых как столбец либо строчка, которая содержит имя человека.

    Наилучшие варианты

    Перед тем, как мы погрузимся в функции Excel, вы в курсе, что Envato Market содержит массу скриптов и плагинов Excel, которые дозволят для вас делать продвинутые функции?

    К примеру, вы сможете:

    Скринкаст

    Если вы желаете следовать инструкциям этого управления, используя собственный файл Excel, вы сможете это создать. Либо, если желаете, скачайте zip файл, прилагаемый к этому управлению, который содержит электрическую таблицу с примером, она именуется vlookup example.xlsx.

    Внедрение VLOOKUP

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

    Неповторимый идентификатор должен быть как серийный номер, не обязано быть 2-ух схожих идентификаторов в одной таблице.

    Синтаксис

    Синтаксис функции VLOOKUP:

    =VLOOKUP(разыскиваемое значение, спектр таблицы, номер столбца, [true/false])

    Вот что означают эти аргументы:

    • Разыскиваемое значение. Ячейка, в которой находится неповторимый идентификатор.
    • Спектр таблицы. Спектр ячеек, которые содержат идентификатор в первом столбце, а в следующих столбцах размещаются другие данные.
    • Номер столбца. Номер столбца, в котором находятся данные, которые для вас необходимы. Не путайте его с буковкой столбца. На рисунке выше, штаты находятся в столбце 4.
    • True/False. Этот аргумент необязателен. True значит, что приемлемо ориентировочное совпадение, а False значит, что допустимо лишь четкое совпадение.

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

    Синтаксис функции VLOOKUP

    Определение имени спектра для сотворения абсолютной ссылки

    В Vlookup example.xlsx поглядите на лист Sales Amounts. Мы введем формула в ячейку B5, потом используем возможность автозаполнения, чтоб скопировать формула в ячейки ниже нее. Это означает, что спектр ячеек в формуле должен быть абсолютной ссылкой. Неплохой метод создать это — это задать имя для спектра ячеек.

    Задание имени спектра в Excel

    1. Перед тем, как вводить формулу, перейдите на лист source data.
    2. Выделите ячейки от A4 (заголовок для столбца Order #) до H203. Резвый метод это создать — это кликнуть по A4, потом надавить Ctrl-Shift-End (Command-Shift-End на Маке).
    3. Кликните снутри поля Name Box (Имя) над столбцом A (на данный момент в поле Name Box отображается A4).
    4. Напечатайте data, потом нажмите Enter.
    5. Сейчас вы сможете применять в формуле имя data заместо $A$4:$H$203.

    Задание имени спектра в Гугл Sheets.

    В Гугл Sheets имя задается мало по другому.

    1. Кликните по заголовку первого столбца ваших начальных данных, потом нажмите Ctrl-Shift-Стрелка на право (Command-Shift-Стрелка на право на Маках). Потом изберите строчку заголовков столбцов.
    2. Нажмите Ctrl-Shift-Стрелка вниз (Command-Shift-Стрелка вниз на Маке). Потом изберите животрепещущие данные.
    3. Кликните в меню Data (Данные), потом изберите Named and protected ranges (Именованные и защищенные спектры).
    4. В поле Name and protected ranges box справа напечатайте data, потом кликните Done.

    Ввод формулы

    Чтоб ввести формулу, перейдите на лист Sales Amounts и кликните по ячейке B5.

    Нажмите Enter.

    Ввод функции VLOOKUP

    Итог должен быть равен 40. Чтоб заполнить значения ниже по столбцу, опять кликните на B5, если нужно. Расположите курсор мыши на точку автозаполнения в правом нижнем углу ячейки, так, чтоб курсор изменил свою форму на перекрестие.

    Когда вы расположите курсор мыши на точку в правом нижнем углу ячейки, он перевоплотится в перекрестие Автозаполнения.

    Кликните два раза, чтоб заполнить значения ниже по столбцу.

    Два раза кликните перекрестием Автозаполнения, чтоб скопировать формулу ниже по столбцу

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

    Внедрение MATCH

    Функция MATCH не возвращает для вас значение из данных; вы задаете значение, которое вы ищете и функция возвращает позицию этого значения. Это как спросить, где находится Мейн стрит, 135, и получить ответ, что это 4-ое здание вниз по улице.

Ссылка на основную публикацию
Adblock
detector