Использование Offset, Vlookup и Concatenate для возврата списка

На моем «Pieces» листе и таблице у меня есть несколько идентификаторов:

Level ID Tenant Type Level SQM Tenant 1358001 Retail 1000 VACANT 1358002 Retail 1000 Schoc 1358003 Retail 1000 VACANT 1358004 Retail 1000 Wishbone 1358005 Retail 1000 Zebrano Ltd 1358006 Retail 1000 Cranfields 1358007 Retail 1000 Astoria 1358008 Retail 1000 Mall Drycleaners 1358009 Unaccounted Area 1000 Unaccounted Area 

Затем у меня есть «передний» лист, где данные сортируются и т. Д. Я хочу иметь возможность перечислять на лицевой панели несколько арендаторов на один уровень. В приведенном выше примере все Арендаторы находятся на одном уровне: «00».

На «переднем» листе я вытягиваю идентификатор уровня, который я просматриваю в ячейке I30, используя =CONCATENATE(F5,B15) : F5= 1358 : B15= 00 .

Ячейка I32 содержит: =COUNTIF(pieces[Level ID],(CONCATENATE(I30,"?"))) возвращает 9 .

Ниже я хочу сделать «динамический» список всех Арендаторов на уровне 00. Я не совсем уверен, как это сделать (и я мог бы идти в неправильном направлении), но я считаю, что у меня есть части головоломки I просто не знаю, как собрать его вместе.

Я могу вернуть одного Арендатора со следующим форумом:

 =VLOOKUP(CONCATENATE(I30,"1"), pieces[[#All], [Level ID]:[Tenant]], 4, FALSE) 

который правильно возвращает первого арендатора («ВАКАНТ»).

Тогда я подумал, что могу вытащить «список» (только в ячейки ниже), используя что-то вроде:

 =OFFSET(VLOOKUP(CONCATENATE(I30,"1"),pieces[[#All],[Level ID]:[Tenant]],4,FALSE),0,0,I32) 

который будет использовать мой оператор COUNTIF чтобы сделать высоту 9. Однако эта формула не работает, и я не могу справиться с ней правильно, даже после поиска

Используйте функцию AGGREGATE¹ для возврата первого, второго, третьего и т. Д. Номеров строк из таблицы в функцию INDEX .

 =IFERROR(INDEX(pieces[Tenant], AGGREGATE(15, 6, (ROW(pieces[Level ID])-ROW(pieces[#Headers]))/(LEFT(pieces[Level ID], LEN(I$30))=I$30), ROW(1:1))), "no more") 

Заполните, пока не закончите матчи. Измените «no more» на строку нулевой длины (например, "" ), если хотите, чтобы ячейки, не соответствующие совпадению, отображались пустыми.

aggregate_next_tenants


¹ Функция AGGREGATE была введена в Excel 2010. Она недоступна в более ранних версиях.

  • Vlookup массив формул в Excel
  • Как вернуть несколько столбцов с помощью vlookup в Excel?
  • Я хочу использовать sumproduct с двумя разными таблицами на основе выбора
  • VLOOKUP больше не работает в Office 2007 - Excel
  • Создание динамических гиперссылок с помощью VLookup с использованием excel
  • VLOOKUP возвращает все НС, хотя некоторые значения находятся в поиске
  • VLOOKUP Возвращает 0, хотя есть значение в строке ниже первого результата
  • Excel VLOOKUP - нужно будет отобразить один столбец с одной вкладки на другую вкладку excel
  • Как использовать VLOOKUP с двумя листами, где строки не соответствуют?
  • Автоматическая популяция листа excel
  • Vlookup, когда целевой столбец имеет несколько текстов
  • Interesting Posts

    Экспорт ASP.NET в Excel – блокировка ячеек

    Ошибка Sub, Function или Property

    Excel: как скопировать часть содержимого ячеек в другие смежные ячейки на соответствующих строках

    Панды python относятся к столбцу с символом "/"

    Как сделать содержимое ячейки Excel невидимым

    Необходимо рассчитать разницу между двумя временными отметками для каждого

    Excel получает среднее значение результата функции index + match, см. Подробности

    Загрузите несколько данных с помощью листа excel в базе данных.

    VSTO Office (Excel) Add-In – WPF XAML Style, ResourceDictionary

    Как получить данные, удовлетворяющие двум условиям в excel?

    openpyxl, пожалуйста, не принимайте текст как число при импорте

    Значения сумм в одном столбце для всех строк с соответствующими данными в другом столбце либо через VBA, либо с помощью комбинации формул / функций

    Рабочая книга VBA выходит за пределы диапазона – ошибка индекса

    Начало работы с написанием файла в Excel в Visual Basic

    Как разрешить ошибку 400 в excel-vba

    Давайте будем гением компьютера.