Как доработать формулу =впр, чтоб поиск шел с нескольких диапазонов?

Question

[Shigobik]

Суть в чем. Есть список людей, дата и есть списки, например, сезонов, разделенных по разным диапазонам.
Я настроил через формулу =впр + еще импорейдж т.к. в моем случае диапазоны на другом документе, раскрывающиеся список из диапазона. Формула берет данные из этого раскрывающегося списка и находит в диапазоне соотстветствие и выводит его ник и число в соседней колонке. Мне не нравится то, что приходится прописывать такие формулы в каждую ячейку, из за чего выводит все, где было найдено, и где нет. Проще говоря, если найдено 3шт, то и хочется, чтоб вывело 3шт, а так из за фомрул надо выписывать все строки из за чего они будут пустыми.

Тут видно список людей и диапазоны, из которых нужно вывести соответствия.
Формула у меня стоит такая, там 3 диапазона, а следовательно мне приходится делать 3 формулы на разные диапазоны...

=IFERROR(VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 1г!L5:М");2;FALSE);"Нет данных")

=IFERROR(VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 2г!O5:P");2;FALSE);"Нет данных")

=IFERROR(VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 3г!R5:S");2;FALSE);"Нет данных")

Следовательно в таком варианте он мне конечный список выведет так. Если он есть только в 3 диапазоне.
Нет данных
Нет данных
Чел 3 3333
Если у меня таких диапазонов будет не 3шт, а 30 условно, и где нить в конце он выведет, а сверху будет пустые строки. Понимаете в чем неудобство? Вот надо обьеденить эти все диапазоны, чтоб он выводил только те, где есть результат.
Т.е. 1 строку
Чел 3 3333

Да и надо как то вывести названия сезонов. Т.е. если чел найден в сезоне 1, то он берет название, сезон 1, затем число.
Я не знаю, возможно ли вообще это обьедеить, мб и нет...подскажите кто знает?

Answer 1

[Владимир Юрченков]

=TEXTJOIN(", "; TRUE;
    IFERROR("Сезон 1: " & VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 1г!L5:М"); 2; FALSE); "");
    IFERROR("Сезон 2: " & VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 2г!O5:P"); 2; FALSE); "");
    IFERROR("Сезон 3: " & VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 3г!R5:S"); 2; FALSE); "")
)

Comments

[Shigobik]

А как она работает? У меня просто пустая ячейка...

[Владимир Юрченков]

Shigobik, ну по идеи пустые он пропускает, если найдено выводит и объединяет через TEXTJOIN

Answer 2

[Shigobik]

Вроде настроил, диапазоны...
Вот только она встроку, ее в столбик сделать получится?
1 столбец сезон, 2 числа.

Comments

[Владимир Юрченков]

Обернуть все в TRANSPOSE()

[Shigobik]

Владимир Юрченков, Типа так?

=TRANSPOSE(TEXTJOIN(", "; TRUE;
    IFERROR("Сезон 1: " & VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 1г!L5:М"); 2; FALSE); "");
    IFERROR("Сезон 2: " & VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 2г!O5:P"); 2; FALSE); "");
    IFERROR("Сезон 3: " & VLOOKUP($О$5;IMPORTRANGE("ссылка на документ"; "Сезон 3г!R5:S"); 2; FALSE); "")
))

А то так не работает, как было строкой, так и осталось.

Answer 3

[Григорий Боев]

Это плохой подход, неправильно организованные таблицы.
1) Несколько таблиц на листе
2) Данные не в плоском виде
3) Есть 3 одинаковых таблицы в разных местах
Все это сгенерировало проблему, которой не должно быть, без этого данные можно получить через FILTER() или QUERY() без ухищрений

=QUERY({ДАННЫЕ}; "
 SELECT * WHERE Col1='"& b5 &"'
")

Чтобы объединить данные с разных источником можно использовать литерал массива или VSTACK()

{
 IMPORTRANGE(таблица1; диапазон1) ;
 IMPORTRANGE(таблица2; диапазон2) ;
 IMPORTRANGE(таблица2; диапазон3) 
}

и подставить это в "ДАННЫЕ" в первую формулу

Comments

[Shigobik]

Ну т.е. мне все равно придется прописывать много раз вот это?

{
 IMPORTRANGE(таблица1; диапазон1) ;
 IMPORTRANGE(таблица2; диапазон2) ;
 IMPORTRANGE(таблица2; диапазон3) 
}

У меня там 24 таких диапазона получается. Тогда уж проще своим методом, по формле в ячейку...