Подробный ответ
Функция ИНДЕКС
ИНДЕКС возвращает значение ячейки по её координатам в массиве:
=ИНДЕКС(A1:C10; 3; 2) — вернёт значение 3-й строки и 2-го столбца диапазона.
Функция ПОИСКПОЗ
ПОИСКПОЗ находит позицию значения в диапазоне:
=ПОИСКПОЗ("Яблоко"; A1:A10; 0) — вернёт позицию первого точного совпадения внутри A1:A10. Для диапазона, начинающегося не с первой строки, позиция отличается от номера строки листа.
Третий аргумент 0 — точное совпадение (как в ВПР).
Связка ИНДЕКС+ПОИСКПОЗ
=ИНДЕКС(C1:C100; ПОИСКПОЗ(E1; A1:A100; 0)) — ищет значение из E1 в столбце A, а возвращает соответствующее значение из столбца C.
Преимущества ИНДЕКС+ПОИСКПОЗ
- Может искать и в левом, и в правом столбце от ключа.
- Ссылки на диапазоны обычно подстраиваются при вставке столбцов; после изменения структуры проверьте их.
- Может повторно использовать результат ПОИСКПОЗ в нескольких формулах; скорость зависит от структуры расчётов и размеров диапазонов.
ПОИСКПОЗможно использовать отдельно.
Двумерный поиск
=ИНДЕКС(B2:D10; ПОИСКПОЗ(G1;A2:A10;0); ПОИСКПОЗ(H1;B1:D1;0)) — находит значение на пересечении нужной строки и столбца, как в таблице умножения.
Практическая деталь
Диапазон поиска и диапазон результата должны начинаться на одной строке и содержать одинаковое число строк. ПОИСКПОЗ возвращает позицию внутри диапазона, а не номер строки листа.
| A | B | C | |
|---|---|---|---|
| 1 | Товар | Цена | Результат поиска |
| 2 | Тетрадь | 80 | 40 |
| 3 | Ручка | 40 |
- C2
=ИНДЕКС(B2:B3;ПОИСКПОЗ("Ручка";A2:A3;0))→ 40
Справочник занимает A2:B3. Формула в C2 ищет название в столбце A и возвращает цену из B либо сообщение об отсутствии товара.