Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Lookup function excel

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

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

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

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

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

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

Как можно использовать функцию LOOKUP (ПРОСМОТР)?

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

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

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

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

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

LOOKUP(lookup_value,lookup_vector,result_vector)
ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

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

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

LOOKUP(lookup_value,array)
ПРОСМОТР(искомое_значение;массив)

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Перемотка видео на ютубе

Пояснение: В данной формуле вместо аргумента lookup_vector (просматриваемый_вектор) подставлено выражение 1/(D2:D8 =LOOKUP(C10,D4:D8,C4:C8)
=ПРОСМОТР(C10;D4:D8;C4:C8)

LOOKUP Function Examples

Excel LOOKUP function examples show how to get value from one-row or one-column range, or from array, with multiple rows and columns.

Video: LOOKUP Function

The LOOKUP function returns a value from a one-row or one-column range, or from an array, which can have multiple rows and columns

To see how the LOOKUP function works, watch this short video tutorial. This video uses the sample workbook from this tutorial.

Your browser can’t show this frame. Here is a link to the page

Uses for LOOKUP

The LOOKUP function can return a result, based on a lookup value, such as:

  1. Find last number in a column
  2. Find latest month with negative amount
  3. Convert student percentages to letter grades
  4. Find a shift number based on an incident time
  5. Find an account number based on exact match
  6. Get store number based on multiple criteria

LOOKUP Syntax

The LOOKUP function has two syntax forms — Vector and Array.

  • With Vector form, it looks for a value in a specified column or row
  • With Array form, it looks in the first row or column of an array.

Vector Form

The Vector form has the following syntax:

  • LOOKUP(lookup_value,lookup_vector,result_vector)
    • lookup_value can be text, number, logical value, a name or a reference
    • lookup_vector is a range with only one row or one column
    • result_vector is a range with only one row or one column
    • lookup_vector and result_vector must be the same size

Array Form

The Array form has the following syntax:

  • LOOKUP(lookup_value,array)
    • lookup_value can be text, number, logical value, a name or a reference
    • searches based on the array dimensions:
      • if there are more columns than rows, it searches in the first row
      • if equal number, or more rows, it searches first column
    • returns value from same position in last row/column

LOOKUP Traps

  • The LOOKUP function doesn’t have an option for Exact Match, which both VLOOKUP and HLOOKUP have. If the lookup value isn’t found, it matches the largest value that is less than the lookup value.
  • The lookup array or vector must be sorted in ascending order, or the result might be incorrect.
  • If the first value in the lookup array/vector is bigger than the lookup value, the result is an #N/A error.

Example 1: Last Number in Column

In the Array form, you can use the LOOKUP function to find the last number in a column.

The specifications in Excel’s Help list 9.99999999999999E+307 as the largest number allowed to be typed into a cell. In this formula, that number is entered as the lookup value. Assuming that large number won’t be found, the last number in column D is returned.

In this example, the numbers in column D do not have to be sorted, and there are text entries included in the column.

=LOOKUP(9.99999999999999E+307,D:D)

Example 2: Latest Negative Amount

This example uses LOOKUP in its Vector form, with sales amounts in column D, and month names in column E. Things didn’t go well for a few months, and there are negative amounts in the sales column.

To find the last month with a negative amount, this LOOKUP formula tests each sales amount to see if it’s less than zero. Then, 1 is divided by that result, and returns either a 1 or a #DIV/0! error.

Читать еще:  Как скачать видеоролик

The lookup value is 2, which won’t be found, so the last 1 is used, to return the month name from column E.

=LOOKUP(2,1/(D2:D8

  • The LOOKUP function won’t find a 2, so it uses the last 1 — the largest value that is less than the lookup value.
  • The last 1 is in the 5th position, so the 5th ID number, in cells B2:B8, is returned.
  • Finally, the IFERROR function returns the text string, «No Match» if the LOOKUP function returns an error. In the screen shot below, the amount 11 is not found, so the formula result is «No match»

    Example 6: Multiple Criteria

    In this example, two criteria are checked — Fruit and Fresh. The store number for the last match is returned. (Thanks to Alex Blakenburg for this example)

    This example uses LOOKUP in a structure that is similar to Example 5.

    =LOOKUP(2,1/((C2:C7=C10)*(D2:D7= D10)),B2:B7)

    • The Lookup value is 2, and cells are tested, to see if they are equal to the criteria cells.
    • Then the test results are multiplied, and return 1 if both are TRUE (1×1=1)
    • If either criterion test is FALSE, the result is 0 (any number x 0 = 0)

  • The LOOKUP function won’t find a 2, so it uses the last 1, in the 6th position. That is the largest value that is less than the lookup value.
  • The 6th store number, in cells B2:B7, is returned.
  • Download the Sample File

    To see the formulas used in these examples, download the LOOKUP function sample workbook. The file is zipped, and is in Excel xlsx file format. There are no macros in the workbook

    LOOKUP Function

    What is the LOOKUP Function?

    The LOOKUP Function is categorized under Excel Lookup and Reference functions. The function performs a rough match lookup either in a one-row or one-column range and returns the corresponding value from another one-row or one-column range.

    While doing financial analysis Financial Analyst Job Description The financial analyst job description below gives a typical example of all the skills, education, and experience required to be hired for an analyst job at a bank, institution, or corporation. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models , if we wish to compare two rows or columns, we can use the LOOKUP function. It is designed to handle the simplest cases of vertical and horizontal lookup.

    The more advanced versions of the LOOKUP function are HLOOKUP and VLOOKUP.

    Formula (Vector)

    There are two forms of Lookup: Vector and Array.

    The vector form of the LOOKUP function will search one row or one column of data for a specified value and then get the data from the same position in another row or column.

    The formula for the function is as follows:

    =LOOKUP(lookup_value, lookup_vector, [result_vector])

    It uses the following arguments:

    1. Lookup_value (required function) – This is the value that we will be searching. It can be a logical value of TRUE or FALSE, reference to a cell, number, or text.
    2. Lookup_vector (required function) – This is the one-dimensional data that we wish to search. Remember, we need to sort it in ascending order.
    3. Result_vector – An optional one-dimensional list of data from which we want to return a value. If supplied, the [result_vector] must be the same length as the lookup_vector. If the [result_vector] is omitted, the result is returned from the lookup_vector.
    Читать еще:  Excel подсчет символов в строке

    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. We need to use this form of LOOKUP when the values that we want to match are in the first row or column of the array.

    Formula (Array) LOOKUP Function

    The arguments are as follows:

    1. Lookup_value (required argument) – This is a value that we are searching for.
    2. Array (required argument) – A range of cells that contains text, numbers, or logical values that we want to compare with the lookup_value.

    How to use the LOOKUP Function in Excel?

    As a worksheet function, the LOOKUP Function can be entered as part of a formula in a cell of a worksheet. To understand the uses of this function, let us consider a few examples:

    Example 1

    Assume we are given a list of products, color, order_id, and quantity. We want a dashboard where we put the product and then we instantly get the quantity.

    The formula to use will be:

    The result we get is:

    Example 2

    Suppose we are in the business of giving loans and we offer different interest rates based on the amount borrowed. We are given the data below:

    The formula to use will be:

    We will get the following result:

    Things to remember about the LOOKUP Function:

    1. #N/A error – Occurs when the Lookup function fails to find the closest match to the supplied lookup_value. This can occur if either:
      1. The smallest value in the lookup_vector (or first column/row of the array) is greater than the lookup_value provided; or
      2. The lookup_vector (or first column/row of the array) is not in ascending order.
    2. #REF! error – Occurs if the formula is attempting to reference cells that are non-existent. This can be caused by either:
      1. Cells being deleted after the Lookup function has been entered.
      2. Relative references in the Lookup function that have become invalid when the functions have been copied to other cells.

    Additional resources

    Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

    • Excel Functions for Finance Excel for Finance This Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
    • Advanced Excel Formulas Course
    • Advanced Excel Formulas You Must Know Advanced Excel Formulas Must Know These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
    • Excel Shortcuts for PC and Mac Excel Shortcuts PC Mac Excel Shortcuts — List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts
    Ссылка на основную публикацию
    Adblock
    detector