Двумерный поиск в таблице (ВПР 2D)

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

Пример 1. Найти значение по товару и городу

Предположим, что у нас имеется вот такой двумерный массив данных по городам и товарам:

vlookup2d1.png

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

  • Во-первых, нам нужно определить номер строки, соответствующей выбранному пользователем в желтой ячейке товару. Это поможет сделать функция ПОИСКПОЗ (MATCH) из категории Ссылки и массивы (Lookup and Reference). В частности, формула ПОИСКПОЗ(J2; A2:A10; 0) даст нам нужный результат (для Яблока это будет число 6). Первый аргумент этой функции - искомое значение (Яблоко из желтой ячейки J2), второй - диапазон ячеек, где мы ищем товар (столбец с товарами в таблице - A2:A10), третий аргумент задает тип поиска (0 - точное совпадение наименования, приблизительный поиск запрещен).
  • Во-вторых, совершенно аналогичным способом мы должны определить порядковый номер столбца в таблице с нужным нам городом. Функция ПОИСКПОЗ(J3; B1:F1; 0) сделает это и выдаст, например, для Киева, выбранного пользователем в желтой ячейке J3 значение 4.
  • И, наконец, в-третьих, нам нужна функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца - функция ИНДЕКС (INDEX) из той же категории Ссылки и массивы (Lookup and Reference). Первый аргумент этой функции - диапазон ячеек (в нашем случае это вся таблица, т.е. B2:F10), второй - номер строки, третий - номер столбца (а их мы определим с помощью функций ПОИСКПОЗ).

Итого, соединяя все вышеперечисленное в одну формулу, получаем для зеленой ячейки решение:

=ИНДЕКС(B2:F10; ПОИСКПОЗ(J2;A2:A10;0); ПОИСКПОЗ(J3;B1:F1;0))

или в английском варианте =INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))

Пример 2. Приблизительный двумерный поиск

Слегка модифицируем предыдущий пример. Предположим, что у нас имеется вот такая ситуация:

vlookup2d2.png

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

Решение для серой ячейки будет практически полностью аналогично предыдущему примеру:

=ИНДЕКС(C7:K16; ПОИСКПОЗ(D3;B7:B16;1); ПОИСКПОЗ(G3;C6:K6;1))

=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))

Разница только в последнем аргументе обеих функций ПОИСКПОЗ (MATCH) - Типу сопоставления (здесь он равен минус 1). Это некий аналог четвертого аргумента функции ВПР (VLOOKUP) - Интервального просмотра (Range Lookup). Вообще говоря, возможных значений для него три:

  • 1 - поиск ближайшего наименьшего числа, т.е. введенные пользователем размеры двери округлялись бы до ближайших наименьших подходящих размеров из таблицы. В нашем случае высота 500 округлилась бы до 450, а ширина 480 до 300, и стоимость двери была бы 135.
  • -1 - поиск ближайшего наибольшего числа, т.е. нестандартная высота 500 округлялась бы до 700, а ширина 480 - до 600 и стоимость составила бы уже 462. Для бизнеса так гораздо интереснее! :)
  • 0 - поиск точного соответствия без каких либо округлений. Используется для 100%-го совпадения искомого значения с одним из значений в таблице. Естественно, применяется при поиске текстовых параметров (как в прошлом примере), т.к. для них округление невозможно.

Важно отметить, что при использовании приблизительного поиска с округлением диапазон поиска - а значит и вся таблица - должна быть отсортирована по возрастанию (для Типа сопоставления = 1) или по убыванию (для Типа сопоставления = -1) по строчкам и по столбцам. Иначе приблизительный поиск корректно работать не будет!

Для точного поиска (Тип сопоставления = 0) сортировка не нужна и никакой роли не играет.

P.S. Обратная задача

В комментах неоднократно интересуются - а как сделать обратную операцию, т.е. определить в первом примере город и товар если мы знаем значение из таблицы? Тут потребуются две небольшие формулы массива (не забудьте ввести их с помощью сочетания клавиш Ctrl+Shift+Enter, а не обычного Enter):

vlookup2d3.png

Принцип их работы следующий:

  1. перебираем все ячейки в диапазоне B2:F10 и ищем совпадение с искомым значением (13) из ячейки J4 с помощью функции ЕСЛИ (IF)
  2. когда нашли совпадение, то определяем номер строки (столбца) первого элемента в таблице в этой строке (столбце) с помощью функций СТОЛБЕЦ (COLUMN) и СТРОКА (ROW)
  3. выдергиваем значение города или товара из таблицы с помощью функции ИНДЕКС (INDEX)

Ссылки по теме



Роман
24.10.2012 23:39:46
О! Спасибо, то что искал. Можно попытаться создать морской бой =)
maxnatiy
24.10.2012 23:40:37
ммм, а я всегда вкладывал ГПР в ВПР.
Сергей
24.10.2012 23:40:59
Подскажите, пожалуйста, есть похожая задача, только сначала нужно выбрать номер таблицы (таблиц 3 штуки, находятся на разных листах), и в зависимости от номера таблицы, извлечь из нее значение по двум другим параметрам. Таблицы абсолютно идентичны, только разные названия и числовые значения.
24.10.2012 23:42:28
Используйте функцию ИНДЕКС. Ее второй вариант позволяет задать не только номер строки и столбца, но и порядковый номер выделенной области, если их несколько.
17.12.2014 10:01:21
Николай, подскажите, пожалуйста, в чем ошибка в формуле. =ИНДЕКС(C7:K16:C21:K30;ПОИСКПОЗ(D3;B7:B16:B21:B30;-1);ПОИСКПОЗ(G3;C6:K6:C20:K20;-1);D4)
Хочу сделать для нескольких таблиц. Но почему то не ищет значение G3.
Спасибо!
30.12.2012 16:39:46
Спасибо Вам большое. А Вы не могли бы подсказать нам ,как это делают чтоб найденная ячейка в таблице сразу окрашивается на зеленый цвет?(первая таблица)
18.04.2013 09:56:12
Выделите таблицу в моем файле примера и выберите Главная - Условное форматирование - Управление правилами (Home - Conditional Formatting - Manage Rules).
27.03.2013 16:46:18
В первом примере красивым способом будет, если использовать метод пересекаемых диапазонов: допустим ячейки, в которых указывается искомый город и товар так и назовем «Город» и «Товар». Потом выделим всю таблицу с данными и быстро присвоим имена всем столбцам и строкам этой таблицы (имена - создать из выделенного фрагмента). А затем в ячейку, куда мы хотим получить результат введем формулу:
=ДВССЫЛ(Товар)  ДВССЫЛ(Город).
18.04.2013 09:55:03
Да, спасибо, это классное решение. Только нужно помнить о том, что в именах городов и товаров может быть пробел, который в этом случае в имени будет заменен на нижнее подчеркивание. Поэтому нужно будет еще замену символов добавить.
18.04.2013 10:59:48
Да, за пробелы как-то не подумала, тогда нужно будет не только пробелы, а еще и дефисы заменить, так как они так же часто встречаются в названиях. Хотя этот вариант однозначно больше интересный, чем практичный (тем более, нельзя, чтоб в конце названий стояли пробелы, при присвоении имени они вообще игнорируются), но формулу я переделала:

=ДВССЫЛ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(товар;" ";"_");"-";"_")) ДВССЫЛ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(город;" ";"_");"-";"_"))
21.04.2013 15:06:05
А как сделать 4D или даже 9D мне вот нужно сделать что то похожее как в статье но по 9ти параметрам((( что не пробовал не получается(
14.08.2013 15:51:04
А как у вас выглядит девятимерная таблица, мне интересно? :)
Думаю, вам нужно копать в направлении функции СУММЕСЛИМН - там до 128 условий поддерживается, но вид таблицы нужен немного другой.
14.08.2013 15:53:28
решил свою проблему получением файла csv с сервера))))
02.07.2013 14:44:02
Как можно прописать пример 1 наоборот?
Ввести 13 в ячейку и получить в одной ячейке "Яблоко", а в другой "Киев"? 13 повторяться внутри таблицы не может.
16.11.2014 11:44:37
Михаил, дописал P.S. к статье на этот счет.
А подскажите а если ситуация как в примере 2 только знаем сколько денег  у человека и нужно найти все возможные размеры дверей
24.11.2013 12:10:36
Тут нужна скорее оптимизация. Копайте в направлении надстройки Поиск решения (Solver).
01.09.2013 18:55:57
А если в первом варианте при вводе товара и города, в таблице несколько значений и нужно выбрать минимальное?
08.03.2014 05:49:19
Подскажите пожалуйста
А как сделать наоборот, вести значение, и получить товар и город ( первый пример)
Спасибо
16.11.2014 11:43:58
Алексей, специально для ответа на этот вопрос дописал P.S. к статье - посмотрите.
24.08.2018 10:19:42
Проверьте пожалуйста дописанный пример для обратного поиска. К сожалению он не рабоатет и выдает ошибку при вводе Ctrl+Shift+Enter.
Так же был бы рад объяснению принципа работы обратного посика. Не могу понять логику посика. Условие IF, при поиске номера строки (столбца) всегда выдает "1" если есть совпадение, и "0" если не найдено совпадений. Не могу понять логику как она должна понять в какой строке (столбце) это происходит.

Судя по комментариям обратный поиск ни у кого не работает.
24.08.2018 11:07:30
Решил вопрос следующим способом (пример для строки, для поиска стобца поменять СТРОКА(ROW) на СТОЛБЕЦ(COLUMN) и заменить ячейки в которой идет поиск по функции INDEX):
{=INDEX(А2:А10;SMALL(IF(NOT(ISERROR(SEARCH(J4;B2:F10)));ROW(B2:F10));1)-ROW(A1);1)}
28.03.2014 21:23:36
А как использовать эту функцию если допустим
Киев и Яблоко в одной строке, в ячейках рядом?
14.11.2014 14:44:15
Николай, а не подскажете как просто выполнить двойной поиск по столбцу и по строке, чтобы результатом поиска было выделение ячейки пересечения (перевод курсора на нее).

Заранее спасибо, с уважением, Олег
15.11.2014 17:23:53
Олег, перемещение активной ячейки можно только макросом. А вот подсветку ячейки можно сделать легко:
  1. выделяете диапазон данных (B2:F10)
  2. открываете Главная - Условное форматирование - Правила выделения ячеек - Равно
  3. щелкаете по ячейке с найденным формулой значением (J4) и выбираете цвет
22.12.2014 07:54:08
Николай здравствуйте, очень полезная информация и я многому научился тут, огромное Вам спасибо! Недавно столкнулся с задачей, мне необходимо подобрать марку рекомендуемого насоса исходя из напора и расхода. Использовал двумерный поиск но увы программа находит только точное совпадение... Что нужно сделать, чтобы подобрать ближайшую к точному значению марку насоса? Задача: Вставить значение(наименование насоса с таблицы) в 1 столбец, если Расход 12580, напор 11. заранее благодарю.
Марка насосаРасход/напор10,811,313
13000Д12500-24н
12500Д12500-24
11500Д12500-24а
14.05.2015 15:02:23
Добрый день!
А как можно найти город и товар, если искомого значения в массиве нет?
Таблица аналогичная, но нужен поиск не точного значения, а  минимально или максимально приближенного к искомому значению?
Уже весь мозг вывихнул...


Не актуально, разобрался
30.06.2016 01:59:55
Если разобрались, то можно было и поделиться... Как раз не ясно..
04.01.2017 09:47:45
А Пример 2 в статье - разве не то, что нужно?
29.07.2016 14:22:47
Николай, а если в таблице как в примере в этой строке два или более одинаковых значений, т.е. 13 стоит и в столбце Питер и в столбце Киев как тогда реализовать вывод данных? У меня похожая ситуация и никак не могу додуматься....выдает ошибку... а когда в строке остается одно значение(второе удаляю), тогда считает правильно...мне нужно вытащить данные как в PS, т.е. сделать, чтобы выдало значение города и товара с одинаковым значением в строке
26.09.2016 17:16:19
Николай добрый день,  подскажите, а если нам известно значение и известен город, но неизвестно наименование продукта или на оборот как быть в этой ситуации?

Значение в массиве могут повторятся но в строке и столбце значение уникально. Как подставив город и значение получить название продукта? Киев/78 = Помидор
04.01.2017 09:46:09
Как-то так:

03.02.2017 16:17:12
Добрый день!
А как при первом варианте суммировать данные?
22.02.2017 10:17:31
Суммировать что с чем?
22.02.2017 10:44:59
Предположим у нас два столбца с названием "Киев" в исходной таблице первого варианта, но цифры разные (к примеру "Киев сентябрь" и "Киев октябрь") и их необходимо просуммировать.
22.02.2017 10:57:39
Эта формула предполагает, что названия городов и месяцев в первой строке-столбце уникальные. Если у вас повторы, то нужно будет разворачивать кросс-таблицу в плоскую и делать по ней сводную.
18.04.2017 08:04:03
А если задать ширину двери ровно 600, то оно округляется до 1000!! И значение из таблицы берется для ширины 1000. Подскажите как сделать чтоб функция округляла больше либо равно?
22.04.2017 10:29:51
Ксения, используйте последний параметр ПОИСКПОЗ равный 1 (а не -1). И таблицу надо отсортировать тогда по возрастанию размеров ширины и высоты.
02.06.2017 11:53:43
Добрый день! У меня в левом столбце идут несколько повторяющихся дат, а в правом разные данные соответствующие этим датам. Как можно из них выбрать нужную?
ДатаВремяДанные
10.04.201715:00108 500
10.04.201716:00108 890
10.04.201717:00108 390
10.04.201718:00108 310
10.04.201719:00108 150
10.04.201720:00108 150
10.04.201721:00108 330
10.04.201722:00108 410
02.06.2017 12:04:49
Нужно выбрать данные в одну строку. Пробовал ПОИСКПОЗ, но так как в левом столбце данные повторяются, то выдается ошибка.
ДатаВремя
0:0021:0022:00
10.04.17108330108410
23.06.2017 11:45:14
Добрый день.
Подскажите, как сделать суммеслимн из подобной таблицы? Например, у меня 2 столбца "Киев", и мне необходимо просуммировать яблоки с двух складов в Киеве. Получается, что для такой формы таблицы нельзя воспользоваться формулой суммеслимн, тогда какое сочетание функций сделать?
07.04.2018 08:44:47
Спасибо большое. Видео очень помогло!
09.04.2018 19:22:35
Меня уже ооочень давно интересует как получить АДРЕС ячейки, в которой находится искомое значение?
11.06.2018 22:33:57
Уважаемые , подскажите. Несовсем понял в последнем примере лог выражения :

СУММ(ЕСЛИ(B2:F10=J9;СТРОКА(B2:F10)-СТРОКА($A$1);0));1)

Что тут сумируется ? СУММ


B2:F10=J9  тут понятно , диапазон значений равен значению в нашей ячейке

СТРОКА(B2:F10)-СТРОКА($A$1);0));1) тут не понимаю , поясните пожалуйста :)
11.10.2018 09:26:24
спасибо большое! Немного переделал последний пример, где обратная задача, т.е. где по вводу цифры выводит название столбца и строки - вместо =J7 в формуле написал +МАКС (), т.е. из массива находит максимальное значение и выводит название строки и столбца. Но столкнулся со следующей проблемой: если максимальных значений несколько, то выдает ошибку. Так же и в вашем примере, если в таблице будет два одинаковых числа и ввести это число, то будет ошибка, Как обойти эту проблему?  
Наверх