Функция ИНДЕКС в Excel (INDEX): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
массив | диапазон |
номер_строки | какая строка |
[номер_столбца] | какой столбец |
Пример
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | ||
| 2 | ШВ-1187 | Шуруповёрт 18 В | 7990 | 4750 | |
| 3 | ДР-2041 | Дрель ударная 750 Вт | 5490 | ||
| 4 | УШМ-0915 | Болгарка 125 мм | 4750 | ||
| 5 | ЛЗ-4410 | Лазерный уровень | 3780 |
Результат в ячейке E2 посчитан LibreOffice на сервере Формулника.
Частые ошибки
номер строки больше размера диапазона
Где работает
| Программа | ИНДЕКС | |
|---|---|---|
| Excel 2010 | есть | |
| Excel 2013 | есть | |
| Excel 2016 | есть | |
| Excel 2019 | есть | |
| Excel 2021 | есть | |
| Excel 2024 | есть | |
| Excel 365 | есть | |
| LibreOffice Calc | есть | |
| Google Таблицы | есть | |
| Яндекс Таблицы | есть | |
| Р7-Офис | есть | |
| МойОфис | есть | |
| MOffice Sheets | есть | проверено |
Похожие функции
ИНДЕКС и ПОИСКПОЗ: поиск по строке и столбцу, два условия, итог до месяца
ИНДЕКС берёт значение по номеру строки и столбца внутри диапазона, а номер подсказывает ПОИСКПОЗ. Для поиска на пересечении товара и месяца нужны два ПОИСКПОЗ: =ИНДЕКС($B$2:$M$50;ПОИСКПОЗ($P$1;$A$2:$A$50;0);ПОИСКПОЗ($P$2;$B$1:$M$1;0)). Ноль в ПОИСКПОЗ означает точное совпадение; без него функция ищет ближайшее меньшее и на несортированных данных ошибается без предупреждения.
По шагам
- Определите, что возвращаете (столбец цен, таблица продаж) — это первый аргумент ИНДЕКС, закрепите его F4.
- Номер строки получите через ПОИСКПОЗ по столбцу ключей той же высоты, что и диапазон ИНДЕКС.
- Если таблица двумерная, номер столбца получите вторым ПОИСКПОЗ по строке заголовков той же ширины.
- Третьим аргументом ПОИСКПОЗ ставьте 0.
- Проверьте формулу на первом и последнем элементе списка — так видно, не сдвинуты ли диапазоны на строку.
Пересечение строки и столбца: цена товара за месяц
Таблица: в A2:A50 товары, в B1:M1 месяцы, внутри — цены. В P1 выбирают товар, в P2 — месяц. Первый ПОИСКПОЗ находит строку товара, второй — столбец месяца, ИНДЕКС берёт число на пересечении. Важно, чтобы ключи начинались с той же строки, что и данные (A2 и B2), а заголовки — с того же столбца (B1 и B2).
=ИНДЕКС($B$2:$M$50;ПОИСКПОЗ($P$1;$A$2:$A$50;0);ПОИСКПОЗ($P$2;$B$1:$M$1;0))Поиск по двум условиям без вспомогательного столбца
Условия перемножаются: строка, где совпали и склад, и артикул, даёт 1, остальные — 0, а ПОИСКПОЗ ищет первую единицу. В Microsoft 365 и Excel 2021 формула вводится обычным Enter, в Excel 2019 и старше — Ctrl+Shift+Enter, иначе вернётся #Н/Д.
=ИНДЕКС($D$2:$D$500;ПОИСКПОЗ(1;($A$2:$A$500=G2)*($B$2:$B$500=H2);0))Итог с начала года до выбранного месяца
ИНДЕКС возвращает не только значение, но и ссылку на ячейку, поэтому его можно поставить концом диапазона. В F1 номер месяца, продажи по месяцам — в B2:B13: формула сложит от января до указанного месяца. При смене F1 диапазон меняется сам, без СМЕЩ и ДВССЫЛ, которые пересчитываются при каждом изменении в книге.
=СУММ(B2:ИНДЕКС(B2:B13;F1))Вся строка: годовой итог по товару
Если номер столбца равен 0, ИНДЕКС возвращает всю строку. Обёрнутая в СУММ, формула даст сумму за 12 месяцев по выбранному товару, а в СРЗНАЧ — среднюю цену за год. Так же номер строки 0 возвращает весь столбец.
=СУММ(ИНДЕКС($B$2:$M$50;ПОИСКПОЗ($P$1;$A$2:$A$50;0);0))Задача → формула
| Задача | Формула |
|---|---|
| Последнее значение столбца без пропусков | =ИНДЕКС(B:B;СЧЁТЗ(B:B)) |
| Последняя цена товара в журнале | =ПРОСМОТР(2;1/($A$2:$A$500=G2);$C$2:$C$500) |
| Название товара с максимальной суммой | =ИНДЕКС(A2:A50;ПОИСКПОЗ(МАКС(D2:D50);D2:D50;0)) |
| Тариф по шкале (пороги по возрастанию) | =ИНДЕКС($I$2:$I$6;ПОИСКПОЗ(B2;$H$2:$H$6;1)) |
| Каждое N-е значение (N в F1) | =ИНДЕКС($B$2:$B$500;(СТРОКА()-1)*$F$1) |
Частые ошибки
- Результат из соседней строки — диапазон ИНДЕКС и диапазон ПОИСКПОЗ начинаются с разных строк, например
C2:C500иA1:A499. Номера строк в них должны совпадать. - Номер строки считают от начала листа — ИНДЕКС считает от первой строки своего диапазона: в
B5:B20строка 1 — это B5. Поэтому номер берут из ПОИСКПОЗ по диапазону той же высоты, а не из СТРОКА(). - ПОИСКПОЗ без третьего аргумента — по умолчанию тип 1 — ближайшее меньшее, нужен отсортированный по возрастанию список. Для поиска по названию или артикулу всегда пишите 0.
- Ищет «Иванов», а находит «ИВАНОВ» — ПОИСКПОЗ не различает регистр и берёт первое совпадение. Если регистр важен, сравнивайте через СОВПАД внутри формулы массива.
Вопросы и ответы
Что значит 0 в ИНДЕКС ПОИСКПОЗ?
Ноль — третий аргумент ПОИСКПОЗ, тип сопоставления «точно». 1 ищет ближайшее меньшее в списке по возрастанию, -1 — ближайшее большее в списке по убыванию.
Чем ИНДЕКС и ПОИСКПОЗ лучше ВПР?
Ищут в любую сторону, не зависят от вставки столбцов и умеют двумерный поиск. Работают во всех версиях Excel, в отличие от ПРОСМОТРX.
Как сделать ИНДЕКС ПОИСКПОЗ по двум критериям?
Перемножьте два сравнения внутри ПОИСКПОЗ и ищите 1. В старых версиях Excel формулу нужно вводить через Ctrl+Shift+Enter.
Почему ИНДЕКС выдаёт #ССЫЛКА!?
Номер строки или столбца больше размера диапазона. Часто это значит, что ПОИСКПОЗ искал в более длинном диапазоне, чем передан в ИНДЕКС.
Как найти значение на пересечении строки и столбца?
ИНДЕКС по всей области данных с двумя ПОИСКПОЗ: по столбцу ключей для строки и по строке заголовков для столбца.
Можно ли настроить такой поиск по моей таблице без разбора формул?
Да, в Формулнике опишите, что найти, или загрузите файл — формула ИНДЕКС+ПОИСКПОЗ будет собрана по вашим диапазонам и проверена на ваших строках.