Функцию ИНДЕКС (на английском INDEX) редко используют саму по себе, а зря: это самый прямой способ сказать Excel «дай мне значение из третьей строки второго столбца». =ИНДЕКС(A2:C6;3;2) вернёт значение на пересечении 3-й строки и 2-го столбца диапазона. Настоящую силу она показывает в паре с ПОИСКПОЗ: =ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0)) находит E2 в столбце A и возвращает значение из столбца C. Это классическая замена ВПР, которая умеет искать влево и работает в любой версии Excel.
| Раздел функций | Ссылки и массивы |
| Название на английском | INDEX |
| Версии Excel | Все версии Excel, включая Microsoft 365 |
| Волатильность | Не волатильная |
| Похожие функции | ПОИСКПОЗ, ВПР, ПРОСМОТРX, СМЕЩ, ВЫБОР |
Синтаксис функции ИНДЕКС
У ИНДЕКС две формы. В 99% случаев нужна первая, для массива:
=ИНДЕКС(массив;номер_строки;[номер_столбца])
- массив — диапазон, из которого берём значение;
- номер_строки — какая по счёту строка внутри диапазона (не номер строки листа!);
- номер_столбца — какой по счёту столбец; если диапазон в один столбец, аргумент можно не указывать.
Вторая форма, ссылочная, принимает несколько областей сразу и четвёртым аргументом выбирает нужную: =ИНДЕКС((A2:B5;D2:E5);2;1;2) вернёт D3, вторую строку первого столбца второй области. Встречается она редко, но знать о ней стоит, чтобы не пугаться при чтении чужих формул.
Важный момент: номера считаются от начала диапазона, а не листа. В диапазоне A5:A20 первая строка — это A5. Отсюда самая частая путаница, о ней ниже в разделе про ошибки.
Как работает ИНДЕКС: строка и столбец
Представьте таблицу как шахматную доску: ИНДЕКС берёт координаты клетки и возвращает то, что в ней стоит.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Товар | Цена | Остаток | Строка | Столбец | Результат | |
| 2 | Чайник | 2490 | 12 | 3 | 2 | 3750=ИНДЕКС($A$2:$C$6;E2;F2) | |
| 3 | Тостер | 1990 | 0 | 5 | 1 | Кофеварка=ИНДЕКС($A$2:$C$6;E3;F3) | |
| 4 | Блендер | 3750 | 5 | 2 | 3 | 0=ИНДЕКС($A$2:$C$6;E4;F4) | |
| 5 | Миксер | 2150 | 8 | ||||
| 6 | Кофеварка | 6900 | 3 |
Третья строка диапазона A2:C6 — это «Блендер», второй столбец — цена, отсюда 3750. Поменяйте номера в E и F, и результат пересчитается.
В английском Excel: =INDEX($A$2:$C$6,E2,F2)
Сами по себе номера строк обычно неизвестны, их и подставляет ПОИСКПОЗ. Об этом следующий раздел.
ИНДЕКС и ПОИСКПОЗ вместо ВПР
Связка работает в два шага: ПОИСКПОЗ находит номер строки с искомым значением, ИНДЕКС берёт значение с тем же номером из нужного столбца. Поскольку столбцы задаются отдельно, искомый может стоять правее результата.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Товар | Артикул | Артикул | Товар | |
| 2 | Чайник | A-1024 | A-1047 | Блендер=ИНДЕКС(A2:A5;ПОИСКПОЗ(D2;B2:B5;0)) | |
| 3 | Тостер | A-1031 | |||
| 4 | Блендер | A-1047 | |||
| 5 | Миксер | A-1052 |
ПОИСКПОЗ(D2;B2:B5;0) возвращает 3: артикул A-1047 стоит третьим. ИНДЕКС(A2:A5;3) берёт третий товар. ВПР так не умеет: она ищет только в первом столбце и возвращает значения правее.
В английском Excel: =INDEX(A2:A5,MATCH(D2,B2:B5,0))
Кроме поиска влево, у связки есть ещё один плюс перед ВПР: вставка нового столбца в таблицу её не ломает, потому что столбец результата задан ссылкой, а не номером. В Excel 2021 и Microsoft 365 то же самое одной функцией делает ПРОСМОТРX, но ИНДЕКС с ПОИСКПОЗ по-прежнему нужны, если файл откроют в старой версии.
Двумерный поиск: строка и столбец по названиям
Если ПОИСКПОЗ подставить и в номер строки, и в номер столбца, получится поиск по матрице: например, продажи конкретного менеджера за конкретный месяц.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Менеджер | Январь | Февраль | Март | Менеджер | Месяц | Продажи | |
| 2 | Иванов | 120 | 135 | 98 | Сидоров | Февраль | 102=ИНДЕКС(B2:D5;ПОИСКПОЗ(F2;A2:A5;0);ПОИСКПОЗ(G2;B1:D1;0)) | |
| 3 | Петрова | 210 | 190 | 225 | ||||
| 4 | Сидоров | 87 | 102 | 115 | ||||
| 5 | Орлова | 164 | 158 | 171 |
Первый ПОИСКПОЗ ищет менеджера в столбце A и возвращает 3, второй ищет месяц в строке заголовков B1:D1 и возвращает 2. На пересечении — 102.
В английском Excel: =INDEX(B2:D5,MATCH(F2,A2:A5,0),MATCH(G2,B1:D1,0))
Обратите внимание: диапазоны для ПОИСКПОЗ начинаются там же, где ИНДЕКС. Строки A2:A5 совпадают со строками B2:D5, столбцы B1:D1 — со столбцами B2:D5. Если сдвинуть один из них, результат съедет на строку или столбец.
Вернуть целую строку или столбец
Ноль вместо номера строки означает «все строки», ноль вместо номера столбца — «все столбцы». Так ИНДЕКС отдаёт целый столбец, который можно сразу передать в СУММ, МАКС или СРЗНАЧ.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Менеджер | Январь | Февраль | Март | Месяц | Сумма | |
| 2 | Иванов | 120 | 135 | 98 | Март | 609=СУММ(ИНДЕКС(B2:D5;0;ПОИСКПОЗ(F2;B1:D1;0))) | |
| 3 | Петрова | 210 | 190 | 225 | |||
| 4 | Сидоров | 87 | 102 | 115 | |||
| 5 | Орлова | 164 | 158 | 171 |
ИНДЕКС(B2:D5;0;3) возвращает весь третий столбец D2:D5, СУММ складывает 98 + 225 + 115 + 171 = 609. Впишите в F2 «Январь», и сумма станет 581.
В английском Excel: =SUM(INDEX(B2:D5,0,MATCH(F2,B1:D1,0)))
В Excel 2021 и Microsoft 365 формула =ИНДЕКС(B2:D5;0;3) в отдельной ячейке просто разольёт столбец вниз. В более ранних версиях её используют внутри других функций, как в примере выше.
Последнее значение в столбце
Частая задача: вывести последнюю запись журнала, последний курс или последний платёж. Если в столбце нет пропусков, номер последней строки равен количеству заполненных ячеек, а его считает СЧЁТЗ.
| A | B | C | |
|---|---|---|---|
| 1 | Платёж | Последний | |
| 2 | 1200 | 640=ИНДЕКС(A:A;СЧЁТЗ(A:A)) | |
| 3 | 850 | ||
| 4 | 2300 | ||
| 5 | 640 |
СЧЁТЗ(A:A) = 5: заголовок и четыре платежа. ИНДЕКС берёт пятую ячейку столбца, то есть A5. Добавьте платёж в A6, и формула сразу его покажет.
В английском Excel: =INDEX(A:A,COUNTA(A:A))
Оговорюсь сразу: если в столбце есть пустые ячейки, СЧЁТЗ их не посчитает и формула промахнётся. Для таких данных в Microsoft 365 надёжнее =ПРОСМОТРX("*";A:A;A:A;;2;-1), который ищет последнее непустое текстовое значение А в любой версии Excel сработает приём =ПРОСМОТР(2;1/(A:A<>"");A:A): он находит последнюю непустую ячейку, будь там текст или число.
Почему ИНДЕКС выдаёт ошибку
- #ССЫЛКА! — номер строки или столбца больше размера диапазона. =ИНДЕКС(A2:C6;7;1) просит 7-ю строку у диапазона из пяти.
- #ЗНАЧ! — номер строки или столбца не число: в ячейку-номер попал текст, например «три» или пустая строка из другой формулы.
- #Н/Д — в связке с ПОИСКПОЗ эту ошибку возвращает именно ПОИСКПОЗ: значение не найдено или забыт третий аргумент 0. Типичные причины — лишние пробелы и числа, сохранённые как текст.
- Результат «соседа» — диапазоны ИНДЕКС и ПОИСКПОЗ начинаются с разных строк: ИНДЕКС(C2:C100) и ПОИСКПОЗ(E2;A1:A100). Номер позиции считается от начала своего диапазона, поэтому всё съезжает на одну строку.
- Формулы «поехали» после протягивания — диапазоны не закреплены. Выделите ссылку и нажмите F4, подробнее в статье про абсолютные и относительные ссылки.
Функция ИНДЕКС — частые вопросы
Что делает функция ИНДЕКС в Excel?
Возвращает значение по номеру строки и столбца внутри диапазона: =ИНДЕКС(A2:C6;3;2) — значение из 3-й строки и 2-го столбца.
Как ИНДЕКС называется на английском?
INDEX. Формула =ИНДЕКС(A2:C6;3;2) в английском Excel — =INDEX(A2:C6,3,2).
Чем ИНДЕКС и ПОИСКПОЗ лучше ВПР?
Связка ищет влево и не ломается при вставке столбцов: =ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0)).
Что значит 0 в ИНДЕКС вместо номера строки?
Весь столбец диапазона. =СУММ(ИНДЕКС(B2:D5;0;2)) сложит второй столбец целиком.
Как получить последнее значение в столбце?
Если в столбце нет пропусков: =ИНДЕКС(A:A;СЧЁТЗ(A:A)).
ИНДЕКС с ПОИСКПОЗ находят всё с первого раза, только если ключи совпадают символ в символ. Лишние пробелы, латиница вместо кириллицы, неявные дубли — !SEMTools найдёт и исправит это прямо в исходных ячейках, без формул и вспомогательных столбцов.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: функция ИНДЕКС в Excel
| Задача | Формула |
|---|---|
| Значение по номеру строки и столбца | =ИНДЕКС(A2:C6;3;2) |
| Поиск вместо ВПР (в том числе влево) | =ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0)) |
| Поиск по строке и столбцу | =ИНДЕКС(B2:D5;ПОИСКПОЗ(F2;A2:A5;0);ПОИСКПОЗ(G2;B1:D1;0)) |
| Сумма выбранного столбца | =СУММ(ИНДЕКС(B2:D5;0;ПОИСКПОЗ(F2;B1:D1;0))) |
| Последнее значение в столбце | =ИНДЕКС(A:A;СЧЁТЗ(A:A)) |
Надеюсь, на этой странице вы нашли то, что искали, и ИНДЕКС перестала казаться бесполезной функцией без ПОИСКПОЗ. Запомните главное: номера считаются от начала диапазона, а диапазоны ИНДЕКС и ПОИСКПОЗ должны начинаться с одной строки. Тогда связка не подведёт ни в старом Excel, ни в новом.