=ВПР(критерий поиска;таблица поиска;ПОИСКПОЗ(критерий;диапазон поиска;0);0)
В большинстве случаев люди используют ВПР в жесткой конструкции поиска по столбцу для извлекаем значения. Но если вы хотите создать динамичный, двусторонний поиск, и если заголовки столбцов в таблице являются уникальными, вы можете использовать функцию ПОИСКПОЗ, чтобы выяснить, какой номер столбца, чтобы дать ВПР. В отличие от обычной формулы ВПР, где индекс столбца жесткий, и не будет работать, если столбцы будут удалены или добавлены в таблицу, ПОИСКПОЗ + ВПР будет работать правильно.
В примере, мы используем эту формулу для динамической подстановки строк и столбцов с помощью ВПР:
=ВПР($H$5;таб2;ПОИСКПОЗ(H6;B4:E4;0);0)
Н5 поставляет подстановки значений для строки, и H6 поставляет подстановки значений для столбца.
Как эта формула работает
Это стандартный точная формула ВПР + ПОИСКПОЗ с одним исключением: индекс столбца снабжен функцией ПОИСКПОЗ.
Обратите внимание, что массив подстановок даны в соответствии с (B4:E4), представляющая заголовки столбцов намеренно включает ячейку В4. Это сделано для того, что число возвращаемых ПОИСКПОЗ в синхронизации с таблицей используется функция ВПР. Другими словами, нужно дать диапазона, который охватывает одинаковое число столбцов с помощью ВПР в таблице. В примере (для Месяц2) ПОИСКПОЗ возвращает значение 3, чтобы после ПОИСКПОЗ, работает формула ВПР выглядит так:
=ВПР(Н5,В5:Е8,3,0)
Что возвращает продажи для Капуста (строка 3) в Месяц2 (столбец 3), который составляет 2311 Руб.
Файл Excel Двусторонний-поиск-с-ВПР-и-ПОИСКПОЗ-devsap