Для тех из вас, кто хорошо разбирается в Excel, вы, скорее всего, очень хорошо знакомы с функцией VLOOKUP . Функция VLOOKUP используется для поиска значения в другой ячейке на основе некоторого соответствующего текста в той же строке.
Если вы все еще не знакомы с функцией VLOOKUP, вы можете проверить мой предыдущий пост о том, как использовать VLOOKUP в Excel.
Несмотря на свою мощь, VLOOKUP имеет ограничение на то, как должна быть структурирована справочная таблица, чтобы формула работала.
Эта статья покажет вам ограничение, в котором нельзя использовать VLOOKUP, и представит другую функцию в Excel, которая называется INDEX-MATCH, которая может решить эту проблему.
INDEX MATCH Excel Пример
Используя следующий пример электронной таблицы Excel, у нас есть список имен владельцев автомобилей и автомобилей. В этом примере мы попытаемся получить идентификатор автомобиля на основе модели автомобиля, указанной под несколькими владельцами, как показано ниже:
На отдельном листе под названием CarType у нас есть простая база данных автомобилей с идентификатором, моделью автомобиля и цветом .
При такой настройке таблицы функция VLOOKUP может работать только в том случае, если данные, которые мы хотим получить, находятся в столбце справа от того, что мы пытаемся сопоставить (поле модели автомобиля ).
Другими словами, с этой структурой таблицы, поскольку мы пытаемся сопоставить ее на основе модели автомобиля, единственная информация, которую мы можем получить, - это цвет (не идентификатор, поскольку столбец идентификатора расположен слева от столбца модели автомобиля ).
Это связано с тем, что в VLOOKUP значение поиска должно отображаться в первом столбце, а столбцы поиска должны быть справа. Ни одно из этих условий не выполняется в нашем примере.
Хорошая новость заключается в том, что INDEX-MATCH сможет помочь нам в достижении этой цели. На практике это фактически объединение двух функций Excel, которые могут работать индивидуально: функции INDEX и функции MATCH .
Тем не менее, для целей этой статьи мы будем говорить только о сочетании этих двух с целью репликации функции VLOOKUP .
Поначалу эта формула может показаться немного длинной и пугающей. Однако, как только вы использовали его несколько раз, вы выучите синтаксис наизусть.
Это полная формула в нашем примере:
= INDEX (CarType $ A $ 2:! $ A $ 5, MATCH (B4, CarType $ B $ 2: $ B $ 5, 0))
Вот разбивка по каждому разделу
= INDEX ( - «=» указывает начало формулы в ячейке, а INDEX - это первая часть функции Excel, которую мы используем.
CarType! $ A $ 2: $ A $ 5 - столбцы на листе CarType, в которых содержатся данные, которые мы хотели бы получить. В этом примере идентификатор каждой модели автомобиля.
MATCH ( - вторая часть функции Excel, которую мы используем.
B4 - Ячейка, содержащая текст поиска, который мы используем ( Модель автомобиля ) .
CarType! $ B $ 2: $ B $ 5 - столбцы на листе CarType с данными, которые мы будем использовать для сопоставления с текстом поиска.
0)) - Чтобы указать, что текст поиска должен точно совпадать с текстом в соответствующем столбце (т. Е. CarType! $ B $ 2: $ B $ 5 ). Если точное совпадение не найдено, формула возвращает # N / A.
Примечание : запомните двойную закрывающую скобку в конце этой функции «))» и запятые между аргументами.
Лично я отошел от VLOOKUP и теперь использую INDEX-MATCH, поскольку он способен делать больше, чем VLOOKUP.
Функции INDEX-MATCH также имеют другие преимущества по сравнению с VLOOKUP :
- Более быстрые расчеты
Когда мы работаем с большими наборами данных, где само вычисление может занять много времени из-за многих функций VLOOKUP, вы обнаружите, что после замены всех этих формул на INDEX-MATCH общий расчет будет выполняться быстрее.
- Нет необходимости считать относительные столбцы
Если в нашей справочной таблице есть ключевой текст, который мы хотим найти в столбце C, а данные, которые нам нужно получить, находятся в столбце AQ, нам потребуется знать / подсчитать, сколько столбцов находится между столбцом C и столбцом AQ при использовании VLOOKUP.,
С помощью функций INDEX-MATCH мы можем напрямую выбрать столбец индекса (т. Е. Столбец AQ), в который нам нужно получить данные, и выбрать столбец для сопоставления (т. Е. Столбец C).
- Это выглядит более сложным
VLOOKUP довольно распространен в наши дни, но немногие знают об совместном использовании функций INDEX-MATCH.
Более длинная строка в функции INDEX-MATCH поможет вам выглядеть как эксперт по обработке сложных и расширенных функций Excel. Наслаждайтесь!
