Альтернативы Excel для VLOOKUP (Содержание)

  • Альтернативы VLOOKUP
  • Примеры функций INDEX и MATCH с ограничением VLOOKUP
  • Как использовать альтернативы VLOOKUP в Excel?

Альтернативы VLOOKUP

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

Однако, если вы думаете, что знание формулы VLOOKUP - это главное в Excel, вы должны немедленно изменить свою мысль (я чувствовал, что знание VLOOKUP - это самое лучшее в начале моей карьеры).

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

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

Примеры функций INDEX и MATCH с ограничением VLOOKUP

Эти две альтернативы VLOOKUP в Excel. Для Альтернативы VLOOKUP нам нужно запомнить номер столбца в случае многих столбцов, но в этих формулах нам не нужно ничего запоминать, нам просто нужно понять логику формулы.

Индекс Формула:

  • Array: Какое значение массива вы хотите получить?
  • Row Num: с какого номера строки вы пытаетесь получить данные.
  • Номер столбца: с какого номера столбца вы пытаетесь получить данные.

Индексная функция - пример № 1

Давайте посмотрим на некоторые примеры Excel Альтернативы VLOOKUP.

Вы можете скачать эту альтернативу шаблону VLOOKUP Excel здесь - Альтернативы шаблону VLOOKUP Excel

У меня есть простая таблица продаж по зонам.

Я могу получить данные с помощью VLOOKUP, но здесь я буду использовать формулу INDEX. Откройте формулу в ячейке E2.

Теперь выберите необходимые значения столбца результата. Здесь мои обязательные значения находятся в диапазоне от B2 до B5. Я выберу диапазон как B2: B5 и заблокирую его.

Теперь мне нужно упомянуть номер строки. Для южного региона номер строки равен 2.

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

INDEX принесет мне данные для южного региона.

Вау!!! INDEX сделал свое дело для меня.

Однако проблема начнется сейчас. Когда я копирую и вставляю формулу в ячейки ниже, мне нужно изменить номер строки при изменении ячейки. В текущей ячейке номер строки равен 1, а когда я перешел в следующую ячейку, он должен быть равен 2. INDEX не принимает инкрементный номер строки автоматически.

Мы можем автоматизировать эту задачу с помощью функции MATCH.

Функция MATCH - Пример № 2

Он вернет номер строки выбранного значения в предоставленном диапазоне.

  • Значение поиска: Какое значение поиска вы пытаетесь найти номер строки?
  • Lookup Array: из какого диапазона вы пытаетесь найти номер строки.
  • Тип соответствия: какой результат вы хотите найти. Является ли это Приблизительное совпадение ниже значения поиска (1) или Точное совпадение (0) или приблизительное совпадение выше значения поиска (-1).

Теперь я буду использовать те же данные из приведенного выше примера. Я постараюсь найти номер строки для каждого региона.

Откройте формулу в ячейке E2 и выберите значение поиска в качестве значения ячейки D2.

Теперь ищите массив, из какой таблицы вы пытаетесь узнать номер строки. Наша таблица массивов от А2 до А5.

Тип совпадения должен быть точным, поэтому я укажу 0 в качестве аргумента.

Я получил номера строк для каждого региона.

Перетащите формулу в оставшиеся ячейки.

Ограничение VLOOKUP - Пример № 3

VLOOKUP всегда работает слева направо. Теперь посмотрите на данные ниже.

Мне нужно извлечь данные о зарплате из диапазона от А1 до D21. Я могу сделать это, применив функцию VLOOKUP.

Результат будет следующим:

Перетащите формулу в оставшиеся ячейки.

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

На основе идентификатора EMP мне нужно извлечь данные, но идентификатор EMP находится в самом правом конце фактических данных. VLOOKUP начинает исчезать в этих случаях.

Таким образом, у нас есть превосходная альтернатива VLOOKUP, называемая функцией INDEX & MATCH.

Как использовать альтернативу VLOOKUP в Excel?

Альтернатива VLOOKUP очень проста и легка. Давайте разберемся, как использовать альтернативы VLOOKUP в Excel с некоторыми примерами.

Функция INDEX + MATCH как альтернатива VLOOKUP - Пример # 1

Теперь возьмите ту же таблицу из приведенного выше примера.

Откройте формулу INDEX в ячейке H2.

Массив - это не что иное, как результат, который вы пытаетесь найти. Здесь мы пытаемся найти значение заработной платы, поэтому выбираем весь диапазон заработной платы.

Теперь нам нужно упомянуть номер строки. В примере с функцией MATCH мы узнали, что функция MATCH может дать нам номер строки. Поэтому я буду использовать функцию MATCH внутри функции INDEX.

LOOKUP означает, на каком основании я пытаюсь найти номер строки. Здесь для идентификатора EMP я пытаюсь найти номер строки, поэтому значение поиска равно EMP ID.

Теперь искать массив - это не что иное, как столбец значений для поиска в основной таблице.

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

Результат будет следующим:

Yessssss !!!!!!! INDEX + MATCH работает точно так же, как VLOOKUP, но может извлекать данные из любого места в любое место.

Перетащите формулу в оставшиеся ячейки.

Функция LOOKUP как альтернатива VLOOKUP - Пример №2

LOOKUP сама по себе является встроенной функцией в Excel.

  • Поиск значения: это значение, на основе которого вы ищете результат.
  • Вектор поиска: это диапазон значений поиска в главной таблице.
  • Вектор результата: это столбец результата в главной таблице.

Возьмите те же данные из предыдущего примера.

Откройте формулу LOOKUP в ячейке H2 и выберите значение поиска как G2.

Теперь нам нужно выбрать вектор поиска от D2 до D21 в основной таблице.

Теперь, наконец, вектор результата должен быть столбцом результата, который мы пытаемся извлечь, это B2 к B21.

После ввода формулы нажмите клавишу ввода, и мы получим результат.

Перетащите формулу в оставшиеся ячейки.

Что нужно помнить об Excel Альтернативы VLOOKUP

  • VLOOKUP может работать только слева направо.
  • Функция MATCH вернет номер строки.
  • Функции INDEX + MATCH и LOOKUP не требуют номера столбца, в отличие от VLOOKUP, для получения данных требуется номер столбца, даже если требуемый столбец уже выбран.
  • Структура данных не имеет значения для функций INDEX + MATCH и LOOKUP. Но для VLOOKUP структура данных имеет значение.

Рекомендуемые статьи

Это было руководство по альтернативам VLOOKUP в Excel. Здесь мы обсуждаем примеры альтернатив Excel для VLOOKUP, таких как INDEX, MATCH и LOOKUP, а также практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -

  1. Функция VLOOKUP в Excel
  2. Учебники для функции MATCH в Excel
  3. Как использовать LOOKUP в Excel?
  4. ИГЕРРОР с VLOOKUP в Excel
  5. Примеры макросов Excel

Категория:

Советы по Excel