В этом руководстве рассказывается об основных преимуществах функций ИНДЕКС и ПОИСКПОЗ в Excel, которые делают их более привлекательными, чем ВПР. Вы увидите несколько примеров формул, которые помогут вам легко справиться со многими сложными задачами, с которыми бессильна функция ВПР.
В нескольких недавних статьях мы сделали все возможное, чтобы объяснить основы функции ВПР начинающим пользователям и показать примеры более сложных формул для опытных пользователей. Сейчас мы попытаемся если не отговорить вас от использования ВПР, то хотя бы показать вам альтернативные способы реализации вертикального поиска в Excel.
Зачем нам это надо? — ты спрашиваешь. Да, потому что ВПР — не единственная функция поиска в Excel, и ее многочисленные ограничения могут помешать вам получить желаемый результат во многих ситуациях. С другой стороны, функции ИНДЕКС и ПОИСКПОЗ более гибкие и имеют ряд особенностей, которые делают их более привлекательными, чем ВПР.
Базовая информация об ИНДЕКС и ПОИСКПОЗ
Поскольку цель этого руководства — показать возможности функций ИНДЕКС и ПОИСКПОЗ для реализации вертикального поиска в Excel, мы не будем подробно останавливаться на их синтаксисе и применении.
Приведем здесь минимум, необходимый для понимания сути, а затем подробно разберем примеры формул, которые показывают преимущества использования ИНДЕКС и ПОИСКПОЗ вместо ВПР.
ИНДЕКС – синтаксис и применение функции
Функция ИНДЕКС в Excel возвращает значение из массива с заданными номерами строки и столбца. Функция имеет следующий синтаксис:
ИНДЕКС(массив,номер_строки,[номер_столбца])
ИНДЕКС(массив,номер_строки,[номер_столбца])
Каждый аргумент имеет очень простое объяснение:
- array (массив) — это диапазон ячеек, из которого вы хотите извлечь значение.
- row_num (номер_строки) — это номер строки в массиве, из которого извлекается значение. Если не указано, аргумент column_num (номер_столбца) является обязательным).
- номер_столбца (номер_столбца) — это номер столбца в массиве, из которого извлекается значение. Если не указано, аргумент row_num (номер_строки) обязателен)
Если указаны оба аргумента, функция ИНДЕКС возвращает значение ячейки на пересечении указанной строки и столбца.
Вот простейший пример функции ИНДЕКС(ИНДЕКС):
=ИНДЕКС(A1:C10,2,3)
=ИНДЕКС(A1:C10,2,3)
Формула ищет диапазон A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть ячейке C2.
Очень просто, правда? Однако на практике не всегда знаешь, какая строка и столбец тебе нужны, и поэтому требуется помощь функции ПОИСКПОЗ.
ПОИСКПОЗ – синтаксис и применение функции
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.
Например, если диапазон B1:B3 содержит значения Нью-Йорк, Париж, Лондон, следующая формула вернет число 3, поскольку «Лондон» является третьим элементом в списке.
=ПОИСКПОЗ(«Лондон»,B1:B3,0)
=ПОИСКПОЗ(«Лондон»,B1:B3,0)
Функция ПОИСКПОЗ имеет следующий синтаксис:
ПОИСКПОЗ (искомое_значение, искомый_массив, [тип_соответствия])
ПОИСКПОЗ (искомое_значение, искомый_массив, [тип_соответствия])
- искомое_значение — это число или текст, который вы ищете. Аргумент может быть значением, включая логическое значение, или ссылкой на ячейку.
- просматриваемый_массив (lookup_array) — диапазон ячеек, в которых выполняется поиск.
- match_type (тип_соответствия): этот аргумент сообщает функции ПОИСКПОЗ, хотите ли вы найти точное или приблизительное соответствие:
- 1 или не указано — находит максимальное значение, меньшее или равное искомому. Искомый массив должен быть отсортирован в порядке возрастания, то есть от меньшего к большему.
- 0 — находит первое значение, равное искомому. Комбинация ИНДЕКС/ПОИСКПОЗ всегда требует точного совпадения, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0.
- -1 Находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть отсортирован по убыванию, то есть от наибольшего к наименьшему.
На первый взгляд полезность функции ПОИСКПОЗ сомнительна. Кому нужно знать позицию элемента в диапазоне? Мы хотим знать стоимость этого предмета!
Напомню, что относительное положение искомого значения (т.е номер строки и/или столбца) — это именно то, что нам нужно указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции ИНДЕКС (INDEX).) функция. Как вы помните, функция ИНДЕКС может возвращать значение, которое находится на пересечении заданной строки и столбца, но не может определить, какая строка и столбец нас интересуют.
Как использовать ИНДЕКС и ПОИСКПОЗ в Excel
Теперь, когда вы знаете основы этих двух функций, я думаю, вам понятно, как функции ПОИСКПОЗ и ИНДЕКС могут работать вместе. ПОИСКПОЗ определяет относительное положение искомого значения в заданном диапазоне ячеек, а ИНДЕКС использует это число (или числа) и возвращает результат соответствующей ячейки.
Вам все еще не совсем ясно? Напишите функции ИНДЕКС и ПОИСКПОЗ следующим образом:
=ИНДЕКС(столбец, из которого мы получаем данные, (ПОИСКПОЗ (искомое значение, столбец, который мы просматриваем, 0))
=ИНДЕКС(столбец для поиска, (ПОИСКПОЗ(искомое значение, столбец для поиска, 0))
Думаю, на примере будет еще проще понять. Предположим, у вас есть следующий список столиц штатов:
Найдем население одной из столиц, скажем, Японии, по следующей формуле:
=ИНДЕКС($D$2:$D$10, ПОИСКПОЗ(«Япония», $B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ(«Япония»;$B$2:$B$10;0))
Теперь давайте разберем, что делает каждый элемент в этой формуле:
- Функция ПОИСКПОЗ ищет значение «Япония» в столбце B, в частности в ячейках B2:B10, и возвращает число 3, поскольку «Япония» занимает третье место в списке.
- Функция ИНДЕКС использует 3 в качестве аргумента row_num, который указывает, из какой строки возвращать значение. Те, вы получаете простую формулу:
=ИНДЕКС($D$2:$D$10,3)
=ИНДЕКС($D$2:$D$10,3)Формула говорит примерно так: посмотрите на ячейки от D2 до D10 и извлеките значение из третьей строки, т.е из ячейки D4, так как отсчет начинается со второй строки.
Вот результат в экселе:
Важно! Количество строк и столбцов в матрице, используемой функцией ИНДЕКС, должно совпадать с аргументами row_num (номер_строки) и column_num (номер_столбца) функции ПОИСКПОЗ. В противном случае результат формулы будет неверным.
Стоп, стоп.. почему нельзя просто воспользоваться функцией ВПР (VLOOKUP)? Есть ли смысл тратить время на то, чтобы разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС?
=ВПР(«Япония»,$B$2:$D$2,3)
=ВПР(«Япония»,$B$2:$D$2,3)
В данном случае нет смысла! Этот пример предназначен исключительно для иллюстрации, чтобы вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают вместе. Следующие примеры покажут вам истинную мощь ИНДЕКС и ПОИСКПОЗ, легко справляясь со многими сложными ситуациями, когда функция ВПР зависает.
Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?
Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС/ПОИСКПОЗ намного лучше, чем ВПР. Однако многие пользователи Excel по-прежнему прибегают к использованию функции ВПР, потому что эта функция намного проще. Это связано с тем, что мало кто полностью понимает все преимущества перехода с ВПР на ИНДЕКС и ПОИСКПОЗ, и никто не хочет тратить время на изучение более сложной формулы.
Ниже я попытаюсь обобщить основные преимущества использования ПОИСКПОЗ и ИНДЕКС в Excel, а вам решать, оставаться ли с ВПР или перейти на ИНДЕКС/ПОИСКПОЗ.
4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:
1. Поиск справа налево. Как известно любому опытному пользователю Excel, ВПР не может смотреть налево, а значит, искомое значение обязательно должно находиться в крайнем левом столбце изучаемого диапазона. В случае ПОИСКПОЗ/ИНДЕКС столбец поиска может быть слева или справа от диапазона поиска. Пример: Поиск значений, которые находятся слева, покажет эту функцию в действии.
2. Безопасное добавление или удаление столбцов. Формулы ВПР перестают работать или возвращают неверные значения, если вы удаляете или добавляете столбец в таблицу поиска. Для функции ВПР любой вставленный или удаленный столбец изменит результат формулы, поскольку синтаксис ВПР требует указать полный диапазон и конкретный номер столбца, из которого вы хотите извлечь данные.
Например, если у вас есть таблица A1:C10 и вы хотите извлечь данные из столбца B, вы должны установить значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, например так:
=ВПР(«искомое значение»,A1:C10,2)
=ВПР(«искомое значение»;A1:C10;2)
Если вы затем вставите новый столбец между столбцами A и B, значение аргумента должно быть изменено с 2 на 3; в противном случае формула вернет результат вновь вставленного столбца.
С помощью ПОИСКПОЗ/ИНДЕКС можно удалять или добавлять столбцы в исследуемый диапазон, не искажая результат, так как непосредственно определяется столбец, содержащий искомое значение. На самом деле это большое преимущество, особенно когда приходится работать с большими объемами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о необходимости исправлять каждую используемую функцию ВПР.
3. Нет ограничений на размер искомого значения. При использовании функции ВПР помните об ограничении длины искомого значения в 255 символов, иначе вы рискуете получить ошибку #ЗНАЧ. (#СТОИМОСТЬ!). Таким образом, если таблица содержит длинные строки, единственным рабочим решением является использование ИНДЕКС/ПОИСКПОЗ.
Предположим, вы используете следующую формулу с функцией ВПР, которая ищет в ячейках с B5 по D10 значение, указанное в ячейке A2:
=ВПР(A2,B5:D10,3,ЛОЖЬ)
=ВПР(A2,B5:D10,3,ЛОЖЬ)
Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо этого вы должны использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:
=ИНДЕКС(D5:D10,СООТВЕТСТВИЕ(ИСТИНА,ИНДЕКС(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10,СООТВЕТСТВИЕ(ИСТИНА,ИНДЕКС(B5:B10=A2,0),0))
4. Увеличена скорость бега. Если вы работаете с небольшими электронными таблицами, скорее всего, разница в производительности Excel не будет заметной, особенно в последних версиях. Если вы работаете с большими таблицами, содержащими тысячи строк и сотни формул поиска, Excel будет работать намного быстрее, если вы используете ПОИСКПОЗ и ИНДЕКС вместо ВПР. В целом эта замена увеличивает скорость работы Excel на 13%.
Влияние ВПР на производительность Excel особенно заметно, если ваша рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что для проверки каждого значения в массиве требуется отдельный вызов функции ВПР. Таким образом, чем больше значений содержит массив и чем больше формул массива содержит ваша таблица, тем медленнее будет работать Excel.
С другой стороны, формула с функциями ПОИСКПОЗ и ИНДЕКС просто выполняет поиск и возвращает результат, выполняя ту же работу намного быстрее.
ИНДЕКС и ПОИСКПОЗ – примеры формул
Теперь, когда вы понимаете, почему стоит изучить функции ПОИСКПОЗ и ИНДЕКС, давайте перейдем к самой интересной части и посмотрим, как можно применить теоретические знания на практике.
Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС
Любой учебник ВПР скажет вам, что эта функция не может смотреть влево. То есть, если искомый столбец не является самым левым столбцом в диапазоне поиска, то шансов получить желаемый результат от ВПР нет.
Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им не важно, где находится столбец с извлекаемым значением. Например, вернемся к таблице со столицами штатов и населением. На этот раз мы напишем формулу ПОИСКПОЗ/ИНДЕКС, которая покажет, какое место занимает столица России (Москва) по численности населения).
Как видно на рисунке ниже, формула отлично справляется с этой задачей:
=ИНДЕКС($A$2:$A$10, ПОИСКПОЗ(«Россия», $B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10, ПОИСКПОЗ(«Россия», $B$2:$B$10,0))
Теперь у вас не должно возникнуть проблем с пониманием того, как работает эта формула:
- Во-первых, мы используем функцию ПОИСКПОЗ, которая находит позицию «Россия» в списке:
=ПОИСКПОЗ(«Россия», $B$2:$B$10.0))
=ПОИСКПОЗ(«Россия», $B$2:$B$10,0)) - Далее мы устанавливаем диапазон для функции ИНДЕКС(INDEX), из которого мы хотим извлечь значение. В нашем случае это A2:A10.
- Затем соединяем обе части и получаем формулу:
=ИНДЕКС($A$2:$A$10,ПОИСКПОЗ(«Россия»,$B$2:$B$10,0))
=ИНДЕКС($A$2:$A$10, ПОИСКПОЗ(«Россия», $B$2:$B$10,0))
Совет: правильное решение — всегда использовать абсолютные ссылки для ИНДЕКС и ПОИСКПОЗ, чтобы диапазоны поиска не терялись при копировании формулы в другие ячейки.
Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)
Вы можете вкладывать другие функции Excel в ИНДЕКС и ПОИСКПОЗ, например, чтобы найти минимальное значение, максимальное значение или ближайшее к среднему значение. Вот несколько вариантов формул относительно примера таблицы выше:
1. МАКС (МАКС). Формула находит максимум в столбце D и возвращает значение в столбце C той же строки:
=ИНДЕКС($C$2:$C$10,ПОИСКПОЗ(МАКС($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10,ПОИСКПОЗ(МАКС($D$2:I$10),$D$2:D$10,0))
Результат: Пекин
2 МИНУТЫ Формула находит минимум в столбце D и возвращает значение в столбце C той же строки:
=ИНДЕКС($C$2:$C$10,ПОИСКПОЗ(МИН($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10,ПОИСКПОЗ(МИН($D$2:I$10),$D$2:D$10,0))
Результат: Лайм
3. СРЕДНЯЯ (СРЕДНЯЯ). Формула вычисляет среднее значение в диапазоне D2:D10, затем находит ближайшее и возвращает значение в столбце C той же строки:
=ИНДЕКС($C$2:$C$10,ПОИСКПОЗ(СРЕДНЕЕ($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10,ПОИСКПОЗ(СРЕДНЕЕ($D$2:D$10),$D$2:D$10,1))
Результат: Москва
О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ
Когда функция СРЗНАЧ используется в сочетании с ИНДЕКС и ПОИСКПОЗ, третий аргумент функции ПОИСКПОЗ обычно равен 1 или -1, если вы не уверены, что искомый диапазон содержит значение, равное среднему значению. Если вы уверены, что такое значение существует, введите 0, чтобы найти точное совпадение.
- Если вы укажете 1, значения в столбце подстановки должны быть отсортированы в порядке возрастания, и формула вернет максимальное значение, меньшее или равное среднему значению.
- Если вы укажете -1, значения в столбце подстановки должны быть отсортированы в порядке убывания, и будет возвращено минимальное значение, большее или равное среднему.
В нашем примере значения в столбце D расположены в порядке возрастания, поэтому мы используем тип соответствия 1. Формула ИНДЕКС/ПОИСКПОЗ возвращает «Москва», поскольку население Москвы ближе всего к медиане (12 269 006).
Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу
Эта формула эквивалентна 2D ВПР и позволяет найти значение на пересечении указанной строки и столбца.
В этом примере формула ИНДЕКС/ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним отличием. Угадай, что?
Как вы помните, синтаксис функции ИНДЕКС позволяет вам использовать три аргумента:
ИНДЕКС(массив,номер_строки,[номер_столбца])
ИНДЕКС(массив,номер_строки,[номер_столбца])
И поздравляю тех, кто это обнаружил!
Начнем с написания шаблона формулы. Для этого возьмем уже известную формулу ИНДЕКС/ПОИСКПОЗ и добавим еще одну функцию ПОИСКПОЗ, которая будет возвращать номер столбца.
=ИНДЕКС(Ваша таблица,(ПОИСКПОЗ(значение для поиска по вертикали, столбец для поиска, 0))(ПОИСКПОЗ(значение для поиска по горизонтали, строка для поиска, 0))
=ИНДЕКС(Ваша таблица,(ПОИСКПОЗ(Значение вертикального поиска, Столбец поиска, 0)),(ПОИСКПОЗ(Значение горизонтального просмотра, Строка поиска, 0))
Обратите внимание, что для двумерного поиска необходимо указать всю таблицу целиком в аргументе array (массив) функции ИНДЕКС (INDEX).
Теперь давайте проверим этот шаблон. Ниже вы можете увидеть список самых густонаселенных стран мира. Предположим, наша задача — узнать численность населения США в 2015 году.
Хорошо, давайте напишем формулу. Когда мне нужно создать сложную формулу в Excel с вложенными функциями, я сначала пишу каждую вложенную отдельно.
Итак, давайте начнем с двух функций ПОИСКПОЗ, которые будут возвращать номера строк и столбцов для функции ИНДЕКС:
- ПОИСКПОЗ для столбца — Ищем в столбце B, точнее в диапазоне B2:B11, значение, которое указано в ячейке H2 (США). Функция будет выглядеть так:
=ПОИСКПОЗ($H$2,$B$1:$B$11,0)
=ПОИСКПОЗ($H$2,$B$1:$B$11,0)Результат этой формулы равен 4, потому что «США» является четвертым элементом списка в столбце B (включая заголовок).
- ПОИСКПОЗ по строке: Ищем значение ячейки H3 (2015) в строке 1, то есть в ячейках A1:E1:
=ПОИСКПОЗ($H$3,$A$1:$E$1,0)
=ПОИСКПОЗ($H$3,$A$1:$E$1,0)Результат этой формулы равен 5, потому что «2015» находится в пятой колонке.
Теперь вставьте эти формулы в функцию ИНДЕКС и вуаля:
=ИНДЕКС($A$1:$E$11, ПОИСКПОЗ($H$2, $B$1:$B$11.0), ПОИСКПОЗ($H$3, $A$1:$E$1.0))
=ИНДЕКС($A$1:$E$11, ПОИСКПОЗ($H$2, $B$1:$B$11,0), ПОИСКПОЗ($H$3, $A$1:$E$1, 0))
Если заменить функции ПОИСКПОЗ значениями, которые они возвращают, формула становится легкой и понятной:
=ИНДЕКС($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11,4,5))
Эта формула возвращает значение на пересечении 4-й строки и 5-го столбца в диапазоне A1:E11, то есть значение в ячейке E4. В одиночестве? Да!
Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ
В руководстве по ВПР мы показали пример формулы с функцией ВПР для поиска по нескольким критериям. Однако существенным ограничением этого решения была необходимость добавления вспомогательного столбца. Хорошей новостью является то, что формула ИНДЕКС/ПОИСКПОЗ может искать значения в двух столбцах без необходимости создавать вспомогательный столбец!
Допустим, у нас есть список заказов и мы хотим найти сумму двух критериев: имени заказчика (Customer) и товара (Product). Дело осложняется тем, что покупатель может купить несколько разных товаров одновременно, а имена покупателей в таблице таблицы поиска расположены в случайном порядке.
Вот формула ИНДЕКС/ПОИСКПОЗ, которая решает задачу:
{=ИНДЕКС(‘Таблица поиска’!$A$2:$C$13,MATCH(1,(A2=’Таблица поиска’!$A$2:$A$13)*
(B2=’Таблица поиска’!$B$2:$B$13),0),3)}
{=INDEX(‘Таблица поиска’!$A$2:$C$13;MATCH(1;(A2=’Таблица поиска’!$A$2:$A$13)*
(B2=’Таблица поиска’!$B$2:$B$13);0);3)}
Эта формула сложнее, чем другие, которые мы обсуждали ранее, но, вооружившись знаниями о функциях ИНДЕКС и ПОИСКПОЗ, вы справитесь с ней. Самая сложная часть — это функция ПОИСКПОЗ, я думаю, ее следует объяснить в первую очередь.
MATCH(1,(A2=’Таблица поиска’!$A$2:$A$13),0)*(B2=’Таблица поиска’!$B$2:$B$13)
MATCH(1;(A2=’Таблица поиска’!$A$2:$A$13);0)*(B2=’Таблица поиска’!$B$2:$B$13)
В приведенной выше формуле искомое значение равно 1, а искомый массив является результатом умножения. Итак, что мы должны умножить и почему? Давайте обо всем по порядку:
- Мы берем первое значение в столбце A (Клиент) на листе основной таблицы и сравниваем его со всеми именами клиентов в таблице листа таблицы поиска (A2: A13).
- Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), в противном случае возвращает 0 (ЛОЖЬ).
- Далее делаем то же самое для значений в столбце B (Product).
- Затем умножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т е оба критерия верны), вы вернете 1. Если оба критерия ложны или только один из них верен, вы вернете 0.
Теперь вы понимаете, почему мы установили 1 в качестве желаемого значения? Правильно, что функция ПОИСКПОЗ возвращает позицию только тогда, когда выполняются оба критерия.
Примечание. В этом случае необходимо использовать необязательный третий аргумент функции ИНДЕКС. Это необходимо, потому что в первом аргументе мы указываем всю таблицу и нам нужно указать функции, из какого столбца извлекать значение. В нашем случае это столбец C (Сумма), поэтому мы вводим 3.
И, наконец, поскольку нам нужно проверять каждую ячейку массива, эта формула должна быть формулой массива. Вы можете увидеть это по фигурным скобкам, которые его окружают. Поэтому, когда вы закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter.
Если все сделано правильно, вы получите результат как на рисунке ниже:
ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel
Как вы, наверное, уже заметили (и не раз), если вы введете неверное значение, например такое, которого нет в просматриваемом вами массиве, формула ИНДЕКС/ПОИСКПОЗ выдает #Н/Д (#Н /A) ошибка или #ЗНАЧ! (#СТОИМОСТЬ!). Если вы хотите заменить это сообщение чем-то более понятным, вы можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.
Синтаксис функции ЕСЛИОШИБКА очень прост:
ЕСЛИ.ОШИБКА(значение,значение_если_ошибка)
ЕСЛИ.ОШИБКА(значение, значение_если_ошибка)
Где значением аргумента (значением) является проверяемое на наличие ошибки значение (в нашем случае результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error — это значение, которое будет возвращено, если формула выдаст ошибку.
Например, вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА следующим образом:
=ЕСЛИ.ОШИБКА(ИНДЕКС($A$1:$E$11,ПОИСКПОЗ($G$2,$B$1:$B$11.0),ПОИСКПОЗ($G$3,$A$1:$E$1.0)),
«Совпадение не найдено. Повторите попытку!») =ЕСЛИ.ОШИБКА(ИНДЕКС($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G $3,$ А$1):$Е$1;0));
«Совпадений не найдено. Пожалуйста, попробуйте еще раз!»)
И теперь, если кто-то введет неверное значение, формула выдаст следующий результат:
Если вы предпочитаете оставлять ячейку пустой при ошибке, вы можете использовать кавычки («») в качестве значения второго аргумента функции ЕСЛИ.ОШИБКА. Мне это нравится:
ЕСЛИ.ОШИБКА(ИНДЕКС(массив,ПОИСКПОЗ(искомое_значение, искомый_массив,0),»»)
ЕСЛИ.ОШИБКА(ИНДЕКС(массив,ПОИСКПОЗ(искомое_значение,искомый_массив,0);»»)
Я надеюсь, что хотя бы одна из формул, описанных в этом уроке, оказалась вам полезной. Если вы нашли другие поисковые задачи, для которых не смогли найти подходящего решения среди информации данного урока, не стесняйтесь описывать свою проблему в комментариях, и мы все вместе постараемся ее решить.