Косвенный с помощью впр в excel

Если вы хотите использовать ВПР и данные, которые вы хотите найти, находятся на разных листах, вы можете объединить их с помощью ДВССЫЛ. Это помогает вам определить несколько диапазонов в одной формуле.

В приведенном выше примере у нас есть ежемесячные данные в трех разных таблицах. Но с помощью всего лишь одного VLOOKUP + INDIRECT вы можете получить количество всех товаров за все месяцы из нескольких листов.

 =VLOOKUP($A2,INDIRECT("'"&B$1&"'!"&"A:B"),2,FALSE)

Чтобы понять эту формулу, нужно разделить ее на две части:

В первой части у нас есть функция ДВССЫЛ, которая создает ссылку на лист, используя имя строки 1. В примере ниже мы ссылаемся на диапазон Яна листа A:B.

Вам необходимо создать структуру в INDIRECT для ссылки на лист с именем и диапазоном, в котором у вас есть данные.

 =INDIRECT("'"&B$1&"'!"&"A:B")

Как только вы переместите формулу в столбец «Февраль», ссылка в ДВССЫЛКЕ переместится на лист «Февраль».

Во второй части VLOOKUP использует адрес диапазона таблицы, возвращенный INDIRECT, и получает его значения на основе col_index_num, указанного в диапазоне.

Важная точка

В приведенной выше формуле вам нужна правильная структура для ссылки на диапазон с именем листа. Если вы введете структуру ниже в КОСВЕННОМ виде:

 "'"&B$1&"'!"&"A:B"

Он вернется:

 "'Jan'!A:B"

Альтернативный метод

INDIRECT — изменчивая функция. Он обновляется при изменении таблицы. Вот почему вы можете рассмотреть возможность использования ВЫБРАТЬ. Например, с помощью CHOOSE вы можете написать три формулы с помощью VLOOKUP.

 =CHOOSE(B$1,VLOOKUP($A2,Jan!$A:$B,2,0),VLOOKUP($A2,Feb!$A:$B,2,0),VLOOKUP($A2,Mar!$A:$B,2,0))

В этой формуле, как я уже сказал, у нас есть три ВПР, и при помощи ВЫБРАТЬ вы можете решить получить результат от любого из ВПР.

В ВЫБОРЕ мы упомянули B1; В строке 1 указаны индексные номера, которые можно использовать для получения значения формулы из SELECT.

Например, если у вас есть 2, CHOOSE вернет значение второго ВПР; из третьего их 3.