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

Если вы еще не знакомы с функцией ВПР^(VLOOKUP) , вы можете прочитать мой предыдущий пост о том, как использовать ВПР в Excel^{(how to use VLOOKUP in Excel)} .

Какой бы мощной она ни была, функция ВПР^(VLOOKUP) имеет ограничение на то, как должна быть структурирована соответствующая справочная таблица, чтобы формула работала.

Эта статья покажет вам ограничение, при котором нельзя использовать функцию ВПР , и представит другую функцию в ^(VLOOKUP)Excel под названием ИНДЕКС-ПОИСКПОЗ^{(INDEX-MATCH)} , которая может решить эту проблему.

INDEX MATCH Пример Excel

Используя следующий пример электронной таблицы Excel , у нас есть список имен владельцев автомобилей и название автомобиля. В этом примере мы попытаемся получить  идентификатор автомобиля^{(Car ID)} на основе модели автомобиля^{(Car Model)} , указанной под несколькими владельцами, как показано ниже:

На отдельном листе под названием CarType у нас есть простая база данных автомобилей с  идентификатором^(ID) , моделью автомобиля^{(Car Model)} и цветом^(Color) .

При такой настройке таблицы  функция ВПР^(VLOOKUP) может работать только в том случае, если данные, которые мы хотим получить, расположены в столбце справа от того, что мы пытаемся сопоставить ( поле « Модель автомобиля »).^{(Car Model )}

Другими словами, с этой структурой таблицы, поскольку мы пытаемся сопоставить ее на основе модели автомобиля^{(Car Model)} , единственная информация, которую мы можем получить, — это цвет^{(Color )} (а не идентификатор^(ID) , поскольку столбец идентификатора^(ID)  расположен слева от  столбца модели автомобиля ).^{(Car Model )}

Это связано с тем, что при ВПР^(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,  где содержатся данные, которые мы хотели бы получить. В этом примере идентификатор^(ID)  каждой модели автомобиля.^{(Car Model.)}

ПОИСКПОЗ(^{(MATCH( )} — вторая часть функции Excel , которую мы используем.

B4 — ячейка, содержащая текст поиска, который мы используем ( модель автомобиля^{(Car Model)} ) .

CarType!$B$2:$B$5 — столбцы на листе CarType   с данными, которые мы будем использовать для сопоставления с текстом поиска.

0)) – Чтобы указать, что искомый текст должен точно совпадать с текстом в соответствующем столбце (т.е. CarType!$B$2:$B$5 ). Если точное совпадение не найдено, формула возвращает #N/A .

Примечание: помните двойную закрывающую скобку в конце этой функции «))» и запятые между аргументами.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Лично я отказался от ВПР^(VLOOKUP) и теперь использую ИНДЕКС-ПОИСКПОЗ, поскольку он способен делать больше, чем ВПР^(VLOOKUP) .

Функции ИНДЕКС-ПОИСКПОЗ^{(INDEX-MATCH)} также имеют другие преимущества по сравнению с ВПР^(VLOOKUP) :

1. Более быстрые расчеты^{(Faster Calculations)}

Когда мы работаем с большими наборами данных, где сам расчет может занять много времени из-за множества функций ВПР^(VLOOKUP) , вы обнаружите, что после замены всех этих формул на ПОИСКПОЗ-ИНДЕКС^{(INDEX-MATCH)} общий расчет будет выполняться быстрее.

1. Нет необходимости считать относительные столбцы^{(No Need to Count Relative Columns)}

Если в нашей справочной таблице ключевой текст, который мы хотим найти, находится в столбце C , а данные, которые нам нужно получить, находятся в столбце AQ , нам нужно будет знать/подсчитать, сколько столбцов находится между столбцом C и столбцом AQ при использовании ВПР.^(VLOOKUP) .

С помощью  функций ИНДЕКС-ПОИСКПОЗ^{(INDEX-MATCH)} мы можем напрямую выбрать столбец индекса (т. е. столбец AQ), где нам нужно получить данные, и выбрать столбец для сопоставления (т. е. столбец C).

1. Выглядит сложнее^{(It Looks More Complicated)}

ВПР^(VLOOKUP) в настоящее время довольно распространена, но мало кто знает о совместном использовании функций ИНДЕКС-ПОИСКПОЗ.

Более длинная строка в функции ИНДЕКС-ПОИСКПОЗ^{(INDEX-MATCH)} поможет вам выглядеть экспертом в работе со сложными и продвинутыми функциями Excel . Наслаждаться!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are wеll-versed in Excel, you are most likely very familiar with thе VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Как исправить ошибки #N/A в формулах Excel, таких как ВПР

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• Как вставить лист Excel в документ Word

• Как использовать анализ «что, если» в Excel

• Сохранять ссылки на ячейки при копировании формулы в Excel

• 3 способа разделить ячейку в Excel

• 5 способов преобразовать текст в числа в Excel

• Как искать в Excel

• Используйте клавиатуру для изменения высоты строки и ширины столбца в Excel

• Как быстро вставить несколько строк в Excel

• Как создать макрос или скрипт VBA в Excel

• Как переместить столбцы в Excel

• Используйте имена динамических диапазонов в Excel для гибких раскрывающихся списков

• 4 способа конвертировать Excel в Google Таблицы

• Быстрое написание чисел в Word и Excel

• 10 советов и рекомендаций по Excel на 2019 год

• Подключение Excel к MySQL

• Центрируйте данные рабочего листа в Excel для печати

• Как сделать круговую диаграмму в Excel