ИНДЕКС и ПОИСКПОЗ в Excel (эксель): поиск влево, примеры

=ИНДЕКС(C2:C6;ПОИСКПОЗ(F2;A2:A6;0)) (по-английски INDEX и MATCH) находит строку, в которой F2 встречается в A2:A6, и возвращает значение из той же строки C2:C6. ПОИСКПОЗ находит позицию, ИНДЕКС достаёт значение на этой позиции. Связка работает в любой версии Excel и умеет искать влево. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.

ФормулыСбросить

G2fx

=INDEX(C2:C6,MATCH(F2,A2:A6,0))

	A	B	C	D	E	F	G
1	Product	Category	Price	Code		Look for	Price
2	Apple	Fruit	$1.20	P-101		Pear	$1.50
3	Pear	Fruit	$1.50	P-102
4	Carrot	Vegetable	$0.80	P-205
5	Bread	Bakery	$2.40	P-310
6	Milk	Dairy	$1.10	P-412

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ИНДЕКС(C2:C6;ПОИСКПОЗ(F2;A2:A6;0))

Замените F2 на Milk, и G2 вернёт $1.10. Замените C2:C6 на B2:B6, и формула вернёт вместо цены категорию.

Как ИНДЕКС и ПОИСКПОЗ работают вместе

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

ФормулыСбросить

G3fx

=INDEX(C2:C6,G2)

	A	B	C	D	E	F	G
1	Product	Category	Price	Code		Look for	Bread
2	Apple	Fruit	$1.20	P-101		Position	4
3	Pear	Fruit	$1.50	P-102		Price	$2.40
4	Carrot	Vegetable	$0.80	P-205
5	Bread	Bakery	$2.40	P-310
6	Milk	Dairy	$1.10	P-412

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ИНДЕКС(C2:C6;G2)

ПОИСКПОЗ(G1;A2:A6;0) возвращает 4, потому что Bread четвёртый элемент A2:A6. ИНДЕКС(C2:C6;4) возвращает четвёртый элемент C2:C6, $2.40. Поставьте ПОИСКПОЗ внутрь ИНДЕКС вместо G2, и получится формула в одной ячейке. Чтобы она работала, нужны два правила:

• Оба диапазона должны начинаться в одной строке и иметь одинаковую высоту. ПОИСКПОЗ(...;A2:A6;0) считает от строки 2, поэтому ИНДЕКС должна читать C2:C6, а не C1:C6 (тогда вернулась бы строка выше).
• Заканчивайте ПОИСКПОЗ нулём. Без него ПОИСКПОЗ выполняет приблизительный поиск, считая столбец A отсортированным, и на списке имён может вернуть позицию не той строки. Три типа сопоставления разобраны на странице ПОИСКПОЗ.

Если значения нет в списке, ПОИСКПОЗ возвращает #Н/Д (по-английски #N/A, так ошибку показывают и таблицы здесь), и вся формула тоже. =ЕСНД(ИНДЕКС(C2:C6;ПОИСКПОЗ(F2;A2:A6;0));"Not found") показывает вместо этого текст.

Поиск влево

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

ФормулыСбросить

G2fx

=INDEX(A2:A6,MATCH(F2,D2:D6,0))

	A	B	C	D	E	F	G
1	Product	Category	Price	Code		Code	Product
2	Apple	Fruit	$1.20	P-101		P-310	Bread
3	Pear	Fruit	$1.50	P-102
4	Carrot	Vegetable	$0.80	P-205
5	Bread	Bakery	$2.40	P-310
6	Milk	Dairy	$1.10	P-412

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ИНДЕКС(A2:A6;ПОИСКПОЗ(F2;D2:D6;0))

P-310 возвращает Bread. Введите P-205 в F2, чтобы получить Carrot. С ВПР пришлось бы сначала перенести столбец Code в начало таблицы.

Двумерный поиск: ИНДЕКС с двумя ПОИСКПОЗ

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

ФормулыСбросить

B8fx

=INDEX(B2:D5,MATCH(B6,A2:A5,0),MATCH(B7,B1:D1,0))

	A	B	C	D
1	Region	Jan	Feb	Mar
2	North	4,200	3,900	4,800
3	South	3,100	3,600	3,300
4	East	5,200	4,700	5,600
5	West	2,800	3,000	3,400
6	Region	East▾
7	Month	Mar▾
8	Sales	5,600

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ИНДЕКС(B2:D5;ПОИСКПОЗ(B6;A2:A5;0);ПОИСКПОЗ(B7;B1:D1;0))

East это строка 3 в A2:A5, а Mar столбец 3 в B1:D1, поэтому ИНДЕКС возвращает строку 3, столбец 3 из B2:D5: 5,600. ПОИСКПОЗ для строки ищет вниз по первому столбцу, ПОИСКПОЗ для столбца ищет вправо по строке заголовков, и оба диапазона выровнены по таблице B2:D5.

Почему ИНДЕКС и ПОИСКПОЗ лучше ВПР

=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))

В русском Excel: =ВПР(F2;A2:D6;3;ЛОЖЬ) и =ИНДЕКС(C2:C6;ПОИСКПОЗ(F2;A2:A6;0)). Обе возвращают цену. Разница проявляется, когда лист меняется:

• Вставка столбца. Вставьте столбец между Category и Price, и ВПР по-прежнему запросит столбец 3, а это теперь новый пустой столбец. В варианте с ИНДЕКС Excel поправит C2:C6 на D2:D6, и формула продолжит работать.
• Поиск влево. Показан выше: ВПР не умеет, ИНДЕКС и ПОИСКПОЗ умеют.

В Excel 2021 и Microsoft 365 ПРОСМОТРX делает и то, и другое одной функцией с более простыми аргументами. ИНДЕКС и ПОИСКПОЗ остаются выбором для файлов, которые должны открываться в Excel 2019 и старше, а сама ИНДЕКС полезна и отдельно. Поиск сразу по двум условиям разобран на странице поиск по нескольким условиям.

Практика: поиск влево

ФормулыСбросить

G2fx

	A	B	C	D	E	F	G
1	Code	Price	Stock	Product		Look for	Price
2	P-101	$1.20	40	Apple		Milk
3	P-102	$1.50	25	Pear
4	P-205	$0.80	60	Carrot
5	P-310	$2.40	15	Bread
6	P-412	$1.10	30	Milk

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

Ваша очередь: Названия товаров стоят в последнем столбце. В G2 верните цену товара из F2 с помощью ИНДЕКС и ПОИСКПОЗ.

ПроверитьПоказать ответ

Практика: двумерный поиск

ФормулыСбросить

B9fx

	A	B	C	D
1	Student	Math	Science	Art
2	Ana	78	85	92
3	Ben	64	71	88
4	Cara	95	89	73
5	Dev	82	67	79
6
7	Student	Cara
8	Subject	Science
9	Score

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

Ваша очередь: В B9 верните балл ученика из B7 по предмету из B8.

ПроверитьПоказать ответ