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

image

Общая формула для поиска всей строки

{=INDEX(table,0,MATCH(lookup value, lookup range),0)}

Примечание: это формула массива с несколькими ячейками, и ее необходимо вводить с помощью CTRL + SHIFT + ENTER при выбранном диапазоне вывода.

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

Значение поиска: ключ для поиска.

Диапазон поиска: строка, содержащая значение поиска. В основном заголовки.

Давайте посмотрим на примере, чтобы прояснить ситуацию.

Пример: получить все номера роликов из таблицы студентов

Здесь у меня есть данные о некоторых студентах в таблице Excel. Я хочу получить весь столбец номера рулона, используя название заголовка Номер рулона

image

Нам нужен вывод в диапазоне F2: F10. Выберите диапазон F2: F10 и напишите эту формулу.

{=INDEX(A2:D10,0,MATCH(F1,A1:D1,0))}

Нажмите CTRL + SHIFT + ENTER. Это делает эту формулу формулой массива с несколькими ячейками.

Вы увидите извлеченный столбец целиком.

image

Как это работает?

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

Давайте разберемся, чтобы понять.

MATCH (F1, A1: D1,0): совпадение ищет значение F1 (номер рулона) в диапазоне A1: D1. Он находится по 2-му индексу. Следовательно, он возвращает 2. Итак, теперь формула = ИНДЕКС (A2: D10,0,2)

ИНДЕКС (A2: D10,0,2): функция ИНДЕКС просматривает 2-й столбец в диапазоне A2: D10 и возвращает все значение столбца в массиве. \ {101; 102; 103; 104; 105; 106; 107; 108 ; 109}.

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

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