Как перевернуть vlookup в excel с помощью index-match?

Чтобы понять, как ИНДЕКС и ПОИСКПОЗ работают как формула обратного поиска, вам нужно выбрать одну простую вещь: «Матч» сообщает индексу позицию (номер ячейки) значения в столбце или строке, затем индекс возвращает это значение. значение, используя эту позицию (номер ячейки) .

Подумайте об этом так: функция ПОИСКПОЗ — это агент под прикрытием, который находит преступника, а функция ИНДЕКС — это полицейский, который впоследствии арестовывает этого преступника.

Но давайте подробно узнаем, как мы можем объединить эти две функции. Ниже приведен синтаксис INDEX, как вы знаете.

ИНДЕКС(массив, номер_строки, [номер_столбца])

В функции ИНДЕКС аргумент номер_строки сообщает, из какой строки следует вернуть значение. Допустим, если вы введете 4, оно вернет значение четвертой строки.

Чтобы создать формулу обратного поиска, нам нужно заменить ПОИСКПОЗ на аргумент_строки ИНДЕКС.

Когда мы используем ПОИСКПОЗ, он ищет значение в столбце поиска и возвращает номер ячейки этого значения. Затем ИНДЕКС использует это число для определения положения ячейки в столбце значений.

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

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

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

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

Часть 1. В первой части мы использовали функцию сопоставления для поиска значения «Мумбаи», и она вернула цифру «5», которая соответствует положению ячейки, в которой находится значение «Мумбаи», в столбце городов.

Часть 2. Во второй части мы использовали ИНДЕКС и обращались к столбцу имени сотрудника для поиска значения. Здесь индексная функция знает, что вам нужно значение 5-й ячейки столбца. Итак, в результате вернулось «Сия».

Дополнительные примеры INDEX и сопоставления

Перед нами стоят некоторые распространенные проблемы, которые мы решили с помощью формулы ИНДЕКС-ПОИСКПОЗ. Доступ к файлам примеров. Обязательно загрузите эти файлы примеров отсюда, чтобы следовать каждому примеру.

1. Базовый поиск с помощью ИНДЕКС – ПОИСКПОЗ.

Обычный поиск — одна из самых важных задач, которые вам необходимо выполнить с формулами поиска, и ПОИСКПОЗ ПО ИНДЕКСУ идеально подходит для этого. Здесь у нас есть таблица данных с идентификатором и именем сотрудника. Каждый идентификатор уникален, и вам нужно искать имя сотрудника по его идентификатору.

Допустим, вы хотите найти имя EMP-132. Для этого формула будет такой:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

вот как работает эта формула

Во-первых , ПОИСКПОЗ сопоставляет идентификатор emp в столбце emp id и возвращает номер ячейки с идентификатором, который вы ищете. Здесь номер строки — 6.

После этого ИНДЕКС возвращает имя сотрудника из столбца имени, используя тот же номер ячейки.

2. Посмотрите налево

ВПР не может идти влево при поиске значения. Как я уже говорил, в ИНДЕКС и ПОИСКПОЗ можно искать в любом направлении. В таблице данных ниже после столбца суммы находится столбец с номером счета.

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

Но здесь, в этой таблице, нам нужно использовать последний столбец таблицы в качестве столбца поиска. Так что палец вниз за VLOOKUP здесь. Давайте вызовем ИНДЕКС и ПОИСКПОЗ для спасения, и формула будет такой:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

…вот как работает эта формула

• Прежде всего , вы упомянули столбец суммы в индексной функции. Это столбец, из которого нам нужно получить значение.
• Во-вторых , в аргументе row_number индексной функции вы использовали функцию сопоставления и указали номер счета-фактуры, сослались на столбец счета-фактуры и использовали ноль для точного соответствия.
• В-третьих , функция match возвращает номер ячейки счета-фактуры из диапазона.

И в конце ИНДЕКС использует это число для возврата суммы, помещая ячейку из столбца суммы.

3. Нечеткий поиск

Как и в случае с VLOOKUP, вы также можете использовать INDEX/MATCH для грубого поиска.

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

И если вы хотите получить оценку 79, вы можете использовать формулу ниже.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

…вот как работает эта формула

В этой формуле мы использовали 1 в функции сопоставления для match_type , что позволяет выполнять приблизительный поиск. Он возвращает первое значение, которое меньше или равно искомому значению.

Для 79 первое наименьшее значение — 75, а для 75 — оценка B. Вот почему в результате вы получаете B.

4. Горизонтальный ПОИСК

Как вы знаете, HLOOKUP предназначен для горизонтального поиска, но для этого вы также можете использовать INDEX и MATCH. Здесь, в таблице данных ниже, у вас есть горизонтальная таблица ежемесячных продаж, и вы хотите получить значение продаж за «май».

И формула будет такая:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

…вот как работает эта формула

В приведенной выше формуле вместо использования ПОИСКПОЗ в аргументе row_num индекса мы использовали его в столбце_number. И match возвращает номер столбца за май.

А затем ИНДЕКС возвращает значение столбца результата на основе номера позиции.

5. Двусторонний поиск

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

Теперь, если вы хотите получить объем продаж продукта в определенной области, вам понадобится двусторонний поиск, и для этого вам нужно использовать комбинацию INDEX MATCH MATCH. Да, здесь вам придется использовать ПОИСКПОЗ дважды.

В обычной комбинации ИНДЕКС и ПОИСКПОЗ вы используете ПОИСКПОЗ для номера строки, но при двустороннем поиске вы также должны использовать его для номера столбца. Формулы будут:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

…вот как работает эта формула

В приведенной выше формуле, чтобы получить сумму продаж из таблицы, вы использовали функцию индекса, а затем функцию сопоставления для ее аргументов номер_строки и номер_столбца. Функция сопоставления, которая является аргументом columns_num, возвращает 5 в качестве значения Product-D в 5-й строке диапазона, на который вы ссылаетесь.

И функция сопоставления, найденная в аргументе row_num, возвращает 2, поскольку значение северной зоны находится во втором столбце диапазона, на который вы ссылаетесь.

Теперь, имея эти значения, индексная функция вернула значение, находящееся во втором столбце и пятой строке: 1456.

6. С учетом регистра

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

И вначале идут одинаковые существительные, но в разных падежах. Например, Джон Паркер и ДЖОН Мэтью. Допустим, вы хотите найти бренды «ДЖОН», а не «Джон», вы можете создать поиск с точным соответствием с помощью ИНДЕКС и ПОИСКПОЗ. И формула будет такая:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

…вот как работает эта формула

Здесь, в этой формуле, вы использовали функцию ТОЧНО в функции сопоставления. Поскольку функция сопоставления не может искать значение с учетом регистра, EXACT является идеальной функцией для этого.

Он может сравнивать два значения и возвращать TRUE, если они абсолютно одинаковы (включая регистр), но вы должны вводить эту формулу в табличной форме, поскольку вам нужно сравнить весь столбец с одним значением в EXACT. Когда вы введете его, он вернет такой массив.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

Отсюда вам нужно использовать функцию сопоставления, чтобы получить позицию TRUE из массива.

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

Важный момент: если вы получите более одного TRUE с помощью EXACT, совпадение просто вернет число для первого TRUE.

7. С подстановочными знаками с INDEX MATCH

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

Просто взгляните на список имен ниже, где указаны имена и фамилии сотрудников, а также их возраст.

Из этого списка нужно получить возраст конкретного сотрудника (Сондра). Но дело в том, что вы знаете только имя.

А если вы используете звездочку, вы можете найти возраст Сондры по имени. Для этого формула будет такой:

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

…вот как работает эта формула

Звездочка — это подстановочный знак, который может заменить n символов. Поэтому, когда вы использовали его после имени, оно заменяло фамилию.

8. Самая низкая стоимость

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

Для этого вы можете использовать функцию МИН с индексом и совпадением, и формула будет такой:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

В столбце А указаны имена учащихся, а в столбце Б — их баллы.

Поэтому, когда вы вставите эту формулу в ячейку и нажмете Enter, она вернет имя ученика с наименьшим баллом, то есть Весы Бастиан.

Объяснение

В этой формуле у нас есть три разные части.

В первой части функция МИН возвращает наименьший балл.

После этого во второй части функция сопоставления возвращает ячейку с наименьшим баллом.

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

Совет: таким же способом вы можете узнать имя ученика, набравшего наивысший балл.

9. Лучший n-й результат

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

Дело в том, что вы не знаете, какой у вас второй по величине балл.

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

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

И формула будет такая:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

…вот как работает эта формула

В этой формуле вы использовали большую функцию внутри функции сопоставления для аргумента искомое_значение. И в большой функции вы упомянули диапазон оценок и 2, чтобы получить второе по величине значение.

После того, как большая функция возвращает второе по величине значение, функция сопоставления использует это значение и возвращает соответствующий номер ячейки.

И в конце индексная функция использует этот номер ячейки и возвращает имя студента.

10. Несколько критериев

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

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

Рассмотрим пример ниже. Здесь у вас есть список продуктов с несколькими деталями, такими как название продукта, категория и размер.

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

Таким образом, формула будет такой:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Примечание. Это формула массива, поэтому вводить ее нужно с помощью ctrl+shift+enter.

…вот как работает эта формула

В этой формуле у вас есть три разных массива для соответствия трем разным значениям, и эти массивы возвращают ИСТИНА и ЛОЖЬ, если значения совпадают.

После этого, когда вы умножаете их друг на друга, вы получаете массив или что-то в этом роде.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

А функция match возвращает позицию 1 для массива.

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

Совет: Если вы не хотите использовать формулу массива, вы можете использовать условие СУММПРОИЗВ .

11. Первое числовое значение диапазона

Допустим, у вас есть список, в котором есть как текстовые, так и числовые значения, и теперь из этого списка вы хотите получить первое числовое значение.

Для этого вы можете объединить функцию ISNUMBER с индексом/соответствием. ISNUMBER может помочь вам определить, какое значение является числом, а какое — текстом.

Формула будет:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

Эту формулу необходимо ввести в виде таблицы (с помощью Ctrl+Shift+Enter).

…вот как работает эта формула

В этой формуле ISNUMBER возвращает массив, равный длине списка, и в этом массиве у вас есть TRUE для значений, которые являются числами, и FALSE для остальных значений.

После этого вы использовали TRUE в функции сопоставления в качестве значения поиска. Таким образом, он возвращает номер позиции первого TRUE в массиве.

В конечном итоге использование этого индекса номера позиции возвращает первое числовое значение.

12. Получить первое непустое значение

Давайте подумаем так: у вас есть список значений, в котором некоторые из первых ячеек пусты, и вы хотите получить первое непустое значение.

И вы можете использовать эту формулу, чтобы получить это первое непустое значение.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

…вот как работает эта формула

Нам нужно разделить эту формулу на три разные части, чтобы понять ее мудро.

Во-первых , вы использовали функцию ЕСПУС в функции сопоставления, чтобы получить массив, в котором у вас есть ИСТИНА для пустых ячеек и ЛОЖЬ для непустых ячеек.

Во-вторых , ПОИСКПОЗ возвращает номер позиции первого значения ИСТИНА в массиве, возвращенном функцией ПУСТОЕ.

Итак, на данный момент у вас есть номер ячейки первого непустого значения.

В-третьих , функция index просто возвращает первое непустое значение в списке.

13. Самый частый текст

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

В списке ниже у вас есть имена.

Но есть имена, которые встречаются более одного раза.

Итак, теперь вам нужно получить имя, которое встречается чаще всего в списке. Вы можете использовать формулу ниже, которая представляет собой комбинацию РЕЖИМ, ИНДЕКС и ПОИСКПОЗ.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Когда вы вставите эту формулу, она вернет «Тамеша», которое является наиболее распространенным именем.

…вот как работает эта формула

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

Давайте возьмем в качестве примера имя «Тамеша», которое является нашим самым распространенным именем в списке. Теперь , если вы посмотрите на список, это произошло сначала на 8-й ячейке, а затем на 12-й ячейке.

Но, если вы посмотрите на таблицу, для всех позиций, где у нас есть «Тамеша», она вернула 8, что является ее первой позицией.

После этого из массива, возвращаемого функцией ПОИСКПОЗ, функция режима вернет наиболее частое число, которое является номером ячейки первого появления « Тамеша ».

И в конце ИНДЕКС вернет текст, используя этот номер ячейки.

14. Создайте гиперссылку

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

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

И для этого мы должны использовать ГИПЕРССЫЛКУ + Ячейку с ИНДЕКСом и ПОИСКПОЗ, и формула будет такой:

Вот как работает эта формула

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

• Прежде всего, вы использовали индекс и совпадение в функции ячейки. И когда вы используете обе функции в функции ячейки, вы получаете ссылку на ячейку соответствующего значения вместо соответствующего значения.
• Во-вторых , вы объединили «#» со ссылкой на ячейку.
• В-третьих , вы снова использовали index и match, чтобы получить соответствующее значение для использования в качестве текста ссылки. Таким образом, у вас будет соответствующее значение, а также ссылка на ячейку, в которой находится это значение.