Поиск нужных данных в диапазоне

Как использовать функцию ВПР (VLOOKUP) для поиска и выборки нужных значений из списка мы недавно разбирали. Если вы еще с ней не знакомы - загляните сюда, не пожалейте пяти минут, чтобы сэкономить себе потом несколько часов.

Если же вы знакомы с ВПР, то - вдогон - стоит разобраться с похожими функциями: ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) , владение которыми весьма облегчит жизнь любому опытному пользователю Excel. Гляньте на следующий пример:

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС( A1:G13 ;ПОИСКПОЗ( C16 ; D1:D13 ;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16 . Последний аргумент функции 0 - означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

Функция ИНДЕКС выбирает из диапазона A1:G13 значение, находящееся на пересечении заданной строки (номер строки с артикулом выдает функция ПОИСКПОЗ) и столбца (нам нужен регион, т.е. второй столбец).

При поиске ближайшего наименьшего (последний аргумент функции ПОИСКПОЗ равен 1) таблица, где ищем, должна быть обязательно отсортирована по возрастанию.При поиске ближайшего наибольшего - по убыванию.

Добрый день всем!Это мой первый комментарий.В первую очередь хотел бы поблагодарить Николая, за его труд, за этот сайт. Все очень доходчиво, структурировано и очень полезно в повседневной работе. Данный ресурс у меня на первом месте во вкладках по эксель!

Ну, а теперь по вопросу если можно, касаемо функции индекс, которая применяется в данном примере.Скажите пожалуйста, а как быть в случае нахождения таблицы в другом соседнем листе.Метод указанный выше работает ровно до 3-го поля функции индекс, где надо указать искомый столбец в виде цифры, откуда мы забираем значение (имя клиента, регион и т.д.) Как корректно выполнить этот этап, чтобы забрать эти значения из соседнего листа?Заранее благодарю за помощь!

Не всегда помогает. Сегодня весь день убил на реализацию этого метода. Все в толк не возьму - или криво офис на комп встал. или одно из двух. то #ссылку возвращает то #н/д . =ИНДЕКС(Диллеры!$A$4:$B$103;C3;2)на одном листе заработало после милионной попытки на другом листе вообще не пашет. почему на первом заработало - непонятно. Просто в какой то момент выдало нужный результат и все. Хотя ничего не трогал в формуле.

Если вставлять ПОИСКПОЗ вообще никак не отрабатывает.Функция по потенциалу понравилась, но как отрабатывает конкретно у меня - нет.ВПР отрабатывает на отлично, но только на одном листе. С другого тоже не хочет хоть разбейся.

Очень понравился Ваш ресурс. Подчерпнул. Спасибо Вам.

Николай, большое спасибо за Ваши уроки! Просмотрев этот урок и скачав Ваш пример, нашёл решение своих задач. В частности вместо указания номера столбца вставил ПОИСКПОЗ

Ещё раз благодарю Вас! С уважением, Вячеслав!

Здравствуйте, а если шапка таблицы многослойная решение есть?Многослойная шапка - например в строке 2 условия в столбце 2 условия а не по одному.

т.е. =ИНДЕКС(Диапазон значений таблицы;ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0);

а мне надо еще 2 условия добавить

ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);

т.е. значение готовое выберется не по 2-м условиям а по четырем

Подскажите пожалуйста как это реализовать в одной формуле.Спасибо!

Вы так быстро ответили , что я таблицу неуспел нарисовать ))

=ИНДЕКС(Диапазон значений таблицы;ПОИСКПОЗ(значение шапки столбца А;диапазон шапки столбца А;0);ПОИСКПОЗ(значение шапки строки 1;диапазон шапки столбца 1;0); Усл 2 q q q h h h Усл 4 x y z x y z список выбора условия 1 k Усл 1 Усл 3 список выбора условия 2 q j b а б в г д е список выбора условия 3 j s е ж з и к л список выбора условия 4 j f м н о п р с решение т k b т у ф х ц ч k s ш щ ъ ы ь э k f ю я - - - - а мне надо еще 2 условия добавить

ПОИСКПОЗ(значение шапки столбца B;диапазон шапки столбца B;0);ПОИСКПОЗ(значение шапки строки 2;диапазон шапки столбца 2;0);

т.е. значение готовое выберется не по 2-м условиям а по четырем Усл 2 q q q h h h Усл 4 x y z x y z список выбора условия 1 k Усл 1 Усл 3 список выбора условия 2 h j b а б в г д е список выбора условия 3 s j s е ж з и к л список выбора условия 4 j f м н о п р с решение #ССЫЛ! k b т у ф х ц ч должно быть ы k s ш щ ъ ы ь э k f ю я - - - -

D1:D13 - это столбец, а не строка

Если вы имели ввиду вопрос "как сделать так, чтобы формула находила не первое встретившееся, а последнее значение", то тут проще всего макросом, наверное - писать на VBA функцию аналогичную ВПР.

Николай, во-первых, хочу сказать Вам огромное спасибо за ваш труд и за ту бесценную информацию, которую Вы двигаете в массы.

Думаю несколько универсализировал формулу в примере (не понимаю как можно прикладывать файлы к сообщению. ):яч. E16 =ИНДЕКС($A$2:$G$13; ПОИСКПОЗ($C$15; $D$2:$D$13; 0); ПОИСКПОЗ(D16; $A$1:$G$1; 0))Потом просто растягиваем. Но, чтобы это работало нужно предварительно задать списки данных для массива D15:D18 - это тоже делает отчет удобнее. Теперь можно "играться" с разными значениями, просто выбирая их из выпадающего списка.

Как реализовать функции ИНДЕКС и ПОИСКПОЗ в VBA ?

При использовании этой формулы в работе с датами, выдает результат 0.1.1900 ( при пустой исходной ячейки) и #Н/Д(в случаях пустых всех заданных диапазонов) Какую формулу можно дописать, чтобы при отсутствии исходных данных выдавал пусто, вместо самой первой даты в экселе?

P/S На функцию еслиошибка реагирует только #Н/Д , а пустую ячейку все равно выдает как 0.1.1900 СПАСИБО:

Добрый день!Помогите пожалуйста по стоить формулу.- есть таблица с данными: список товаров и столбцы магазинов с оборотами по ним-среднее выводиться с отдельную ячейку С69

задача выводить рядом со средним какой товар = среднему значению и рядом какой магазин

B3:B61= это товары , С69 искомое значение , C3:C61= столбец магазинов где ищет.(НО ИХ 20)

Проблема в том, что выводиться только по одной колонке в формуле, а необходимо искать по всем 20.

Добрый день! Простите, если глупость спрашиваю, но как из столбца цифр выбрать (просуммировать) только те, которые одновременно больше например, 10, но меньше 20.

Т.е. как-то так: СУММЕСЛИ(А2:А30; И(">=10"; "<20") ) Понимаю, что написанное красным неправильно. подскажите как одновременно учесть оба условия. СУММЕСЛИМН тоже лишь перечисляет критерии, выбирая сначала все числа больше 10, а потом ещё все меньше 20, а нужно только числа от 10 до 20.

Добрый день. вопросне получается найти значение через эти формулы.может тогда подскажете решение?Есть строка (не столбец) значений: 2030, 2000, 2050, 2100, 2000.среднее значение их 2036мне нужно рядом с ними сделать выборку значения максимально приближенного к среднему значению - и этим значением является 2030ИНДЕКС+ПОИСКПОЗ - находит почему-то только 2000 (при значении"+1")при замене на "-1" - выдает Н/Д0 - даже не ставлю, т.к. точного значения в строке нет